All Forums Analytics
psyam 5 posts Joined 02/08
16 Jul 2009
small SQl query

I have a table with dataColumn 1 Column 2A 1A 2A 3B 1B 2 C 4c 6c 7i need to get the output as (based on group by first column)A 1 2 3B 1 2C 4 6 7Please let me know the SQL to achieve this

sk73 52 posts Joined 07/09
22 Jul 2009

hi ...I tried out this in MS-Access. The query came out as below:TRANSFORM Max(Table.Col2) AS MaxOfCol2SELECT Table.Col1 AS [COL1]FROM [Table]GROUP BY Table.Col1PIVOT 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

Jimm 298 posts Joined 09/07
23 Jul 2009

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.SelectId, 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 CheckValFrom (Select Col1 , Col2 , Row_Number () Over (Partition By Col1 Order By Col2) as PosNFrom tbl_with_data) As D1Group By 1;

sk73 52 posts Joined 07/09
23 Jul 2009

thank you Jim!!!Great information too.

24 Jul 2009

we can also try with Recursive SQLrefer to the below linkhttp://www.teradata.com/tdmo/v06n03/Tech2Tech/InsidersWarehouse/OddballSQLTricks.aspxhttp://www.teradata.com/tdmo/v06n03/Tech2Tech/InsidersWarehouse/OddBallSqlTricksCont.aspx

sk73 52 posts Joined 07/09
24 Jul 2009

Hi Rahul,Thank you.I am on my way to figure out some thing from there.

sk73 52 posts Joined 07/09
29 Jul 2009

hi...I used the following procedure:1. Count the number of records2. 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.

eejimkos 73 posts Joined 01/12
29 Jul 2012

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.

 

 

You must sign in to leave a comment.