Code: |
FAMILY_ID RELATION_ID FIRST_NAME LAST_NAME 123456 0 HOBO KELLY 123456 1 BOZO CLOWN 123456 6 YUCKO CLOWN 123456 9 SIDESHOW BOB 654321 0 ROY RODGERS 654321 2 DALE EVANS 654321 3 CISCO KID 654321 4 LUCAS McCAIN 654321 9 HOPALONG CASSIDY |
Code: |
SELECT Z.FAMILY_ID, MAX(DECODE(Z.RELATIONID_RANK, 01, Z.FIRST_NAME || ' ' || Z.LAST_NAME)) AS D1, MAX(DECODE(Z.RELATIONID_RANK, 02, Z.FIRST_NAME || ' ' || Z.LAST_NAME)) AS D2, MAX(DECODE(Z.RELATIONID_RANK, 03, Z.FIRST_NAME || ' ' || Z.LAST_NAME)) AS D3, MAX(DECODE(Z.RELATIONID_RANK, 04, Z.FIRST_NAME || ' ' || Z.LAST_NAME)) AS D4, MAX(DECODE(Z.RELATIONID_RANK, 05, Z.FIRST_NAME || ' ' || Z.LAST_NAME)) AS D5, MAX(DECODE(Z.RELATIONID_RANK, 06, Z.FIRST_NAME || ' ' || Z.LAST_NAME)) AS D6, MAX(DECODE(Z.RELATIONID_RANK, 07, Z.FIRST_NAME || ' ' || Z.LAST_NAME)) AS D7, MAX(DECODE(Z.RELATIONID_RANK, 08, Z.FIRST_NAME || ' ' || Z.LAST_NAME)) AS D8, MAX(DECODE(Z.RELATIONID_RANK, 09, Z.FIRST_NAME || ' ' || Z.LAST_NAME)) AS D9, MAX(DECODE(Z.RELATIONID_RANK, 10, Z.FIRST_NAME || ' ' || Z.LAST_NAME)) AS D10 FROM (SELECT FAMILY_ID, RELATION_ID, FIRST_NAME, LAST_NAME, RANK() OVER ( PARTITION BY FAMILY_ID ORDER BY FAMILY_ID , RELATION_ID ) AS RELATIONID_RANK FROM YOUR.TABLE WHERE FAMILY_ID IN (123456, 654321) ORDER BY FAMILY_ID, RELATION_ID ) Z GROUP BY Z.FAMILY_ID; |
Code: |
123456 HOBO KELLY BOZO CLOWN YUCKO CLOWN SIDESHOW BOB 654321 ROY RODGERS DALE EVANS CISCO KID LUCAS McCAIN HOPALONG CASSIDY |
NASCAR9 wrote: |
The reason I needed columns was for an Insert into another table |
Code: |
INSERT INTO New_Table SELECT FAMILY_ID ,CHAR(RTRIM(IFNULL(FIRST_NAME, ' ')) || CHAR(' ') || RTRIM(IFNULL(LAST_NAME, ' '))) AS FULLNAME FROM Old_table WHERE FAMILY_ID IN (123456, 654321) ; |
NASCAR9 wrote: |
kolusu,
The point of my post was to show how to take horizonal data and be able to retrieve the data into individual columns. This query is only an example I'm sharing. |
output generated using printer-friendly topic mod. All times are GMT - 5 Hours