small SQl query

Analytics
Enthusiast

small SQl query

I have a table with data
Column 1 Column 2
A 1
A 2
A 3
B 1
B 2
C 4
c 6
c 7

i need to get the output as (based on group by first column)
A 1 2 3
B 1 2
C 4 6 7
Please let me know the SQL to achieve this
7 REPLIES
Enthusiast

Re: small SQl query

hi ...

I tried out this in MS-Access. The query came out as below:

TRANSFORM
Max(Table.Col2) AS MaxOfCol2
SELECT Table.Col1 AS [COL1]
FROM [Table]
GROUP BY Table.Col1
PIVOT Table.Col2;

I am not familiar with the in-built functions of Teradata's SQL. Basically, I am learning it. I feel I will become familiar with it day-by-day.

I am not sure about the answer. Please, send me a message if you find an answer.

Thanks...

Regards,
SK
Enthusiast

Re: small SQl query

Teradata does not have a Transform or Pivot function - they are regarded as presentation level functions and it relies on the presentation tool (Cognos/ Access/ BO/ etc) to do that.

The SQL below will Pivot up to 10 columns. It also give you a column which tells you whether you have enough columns! You can extend it if you want more, but you have to have a finite maximum number of columns.

This returns Null for empty columns, but you can easily change this.

Select
Id
, Max(CASE WHEN PosN = 1
THEN Col2 END) As Val1
, Max(CASE WHEN PosN = 2
THEN Col2 END) As Val2
, Max(CASE WHEN PosN = 3
THEN Col2 END) As Val3
, Max(CASE WHEN PosN = 4
THEN Col2 END) As Val4
, Max(CASE WHEN PosN = 5
THEN Col2 END) As Val5
, Max(CASE WHEN PosN = 6
THEN Col2 END) As Val6
, Max(CASE WHEN PosN = 7
THEN Col2 END) As Val7
, Max(CASE WHEN PosN = 8
THEN Col2 END) As Val8
, Max(CASE WHEN PosN = 9
THEN Col2 END) As Val9
, Max(CASE WHEN PosN = 10
THEN Col2 END) As Val10
, Max(CASE WHEN PosN > 10
THEN 'You Need More Columns!'
Else '' END) As CheckVal
From
(Select
Col1
, Col2
, Row_Number () Over (Partition By Col1
Order By Col2) as PosN
From tbl_with_data
) As D1
Group By 1
;
Enthusiast

Re: small SQl query

thank you Jim!!!
Great information too.
Enthusiast

Re: small SQl query

we can also try with Recursive SQL
refer to the below link
http://www.teradata.com/tdmo/v06n03/Tech2Tech/InsidersWarehouse/OddballSQLTricks.aspx
http://www.teradata.com/tdmo/v06n03/Tech2Tech/InsidersWarehouse/OddBallSqlTricksCont.aspx
Enthusiast

Re: small SQl query

Hi Rahul,

Thank you.

I am on my way to figure out some thing from there.
Enthusiast

Re: small SQl query

hi...

I used the following procedure:

1. Count the number of records
2. Create a new table with columns equal to (count-1)
3. Access the original table's recordset and place it into the newly created table only if the original table's values is not null.

I could not write a query though.
Enthusiast

Re: small SQl query

Hi,

the solution is either Recursive sql  or with serial left join on the column that you want to have all the rows from your chain.