November 12, 2014 at 5:31 am
Hi all,
I have the below data extract:
ACCOUNT CODE
C217831A34FA436AA55D30A1CB9141EAWP_2
C217831A34FA436AA55D30A1CB9141EACT_3
C217831A34FA436AA55D30A1CB9141EADA_128
C217831A34FA436AA55D30A1CB9141EANM_1
I need to transpose the data so instead of having 4 rows, I have only 1 row with four columns i.e.:
ACCOUNT CODE1 CODE2 CODE3 CODE4
C217831A34FA436AA55D30A1CB9141EA WP_2 CT_3 DA_128 NM_1
Many thanks
November 12, 2014 at 6:16 am
Use case to pivot the data, ie
WITH cte (ACCOUNT,CODE,ColNo) AS (
SELECT ACCOUNT,CODE,
ROW_NUMBER() OVER (PARTITION BY ACCOUNT ORDER BY CODE)
FROM
)
SELECT ACCOUNT,
MAX(CASE WHEN ColNo=1 THEN CODE END) AS [CODE1],
MAX(CASE WHEN ColNo=2 THEN CODE END) AS [CODE2],
MAX(CASE WHEN ColNo=3 THEN CODE END) AS [CODE3],
MAX(CASE WHEN ColNo=4 THEN CODE END) AS [CODE4]
FROM cte
GROUP BY ACCOUNT
This will only handle the first 4 codes for each account
If there is no maximum you will have to generate a dynamic query
Far away is close at hand in the images of elsewhere.
Anon.
November 12, 2014 at 8:03 am
Ah brilliant, much appreciated 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply