Data Analytics SQL Query method

Question

I need every 13th row as new column. I am not sure how this can be done using Pivot or transpose. Number of rows are not fixed.

Please guide me the way.

Thanks

Data is as shown below where every 4 row gets repeated

ID Value
AA A
BB B
CC C
AA D
BB E
CC F
AA G
BB H
CC I

it should be as below

ID Value1 Value2 Value3
AA A          D          G
BB B          E          H
CC C         F           I

Sample data and expected results please. – wBob Jul 30 '16 at 17:15 — wBob, Jul 30 '16 at 17:15

score 1 · Accepted Answer · answered Jul 30 '16 at 18:05

1

You can try PIVOT, something like this:

DECLARE @t TABLE ( ID CHAR(2), Value CHAR(1) )

INSERT INTO @t
SELECT *
FROM (
VALUES
( 'AA', 'A' ),
( 'BB', 'B' ),
( 'CC', 'C' ),
( 'AA', 'D' ),
( 'BB', 'E' ),
( 'CC', 'F' ),
( 'AA', 'G' ),
( 'BB', 'H' ),
( 'CC', 'I' )
)   x ( y, z )

SELECT *
FROM
    (
    SELECT *, ROW_NUMBER() OVER( PARTITION BY ID ORDER BY [VALUE] ) rowId
    FROM @t
    ) x
PIVOT ( MAX( Value ) FOR rowId In ( [1], [2], [3] ) ) pvt

My results:

answered Jul 30 '16 at 18:05

wBob

10,380
2
24
44

Thanks Bob. That is perfect and within no time... Thanks once again. – Prasad Jul 30 '16 at 18:15
It's probably worth mentioning the query is not dynamic. – wBob Jul 30 '16 at 19:56

Data Analytics SQL Query method

1 Answers1