Link to home
Start Free TrialLog in
Avatar of vesbos
vesbosFlag for United States of America

asked on

How can I Convert Rows to Columns in SQL.

How can I Convert Rows to Columns in SQL.

I  would  like to transform the data from rows to columns.

10 rows in 10 fileds as one record per ID/seq

ID              Seq    I
O35018    1    221
O35018    2    239
O35018    3    213
O35018    4    195
O35018    5    228
O35018    6    216
O35018    7    209
O35018    8    183
O35018    9    205
O35018    10    187
O35018    11    211
O35018    12    161
O35018    13    163


like:

ID             Seq    I1       I2        I3       I4         I5       I6        I7       I8        I9      I10
O35018    1    221    239    213    195    228    216    209    183    205    187
O35018    2    211    161    163    188    186    162    178    161    171    176
O35018    3    165    172    150    152    143    153    143    142    132    144


Avatar of ste5an
ste5an
Flag of Germany image

You can use the PIVOT clause:

SELECT ID,
       [1] AS I01, 
       [2] AS I02, 
       [3] AS I03, 
       [4] AS I04, 
       [5] AS I05, 
       [6] AS I06, 
       [7] AS I07, 
       [8] AS I08, 
       [9] AS I09, 
       [10] AS I10
FROM   yourTable
PIVOT  ( 
              SUM(I) 
              FOR Seq 
              IN ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10] )
       ) AS P;

Open in new window

Avatar of Bill Prew
Bill Prew

Not too elegant, but you could try this approach:

CREATE TABLE T1 (
    ID   VARCHAR(10),
    SEQ  INTEGER,
    I    INTEGER
);

INSERT INTO T1 VALUES
    ('O35018',  1, 221),
    ('O35018',  2, 239),
    ('O35018',  3, 213),
    ('O35018',  4, 195),
    ('O35018',  5, 228),
    ('O35018',  6, 216),
    ('O35018',  7, 209),
    ('O35018',  8, 183),
    ('O35018',  9, 205),
    ('O35018', 10, 187),
    ('O35018', 11, 211),
    ('O35018', 12, 161),
    ('O35018', 13, 163),
    ('O35018', 14, 188),
    ('O35018', 15, 186),
    ('O35018', 16, 162),
    ('O35018', 17, 178),
    ('O35018', 18, 161),
    ('O35018', 19, 171),
    ('O35018', 20, 176),
    ('O35018', 21, 165),
    ('O35018', 22, 172),
    ('O35018', 23, 150),
    ('O35018', 24, 152),
    ('O35018', 25, 143),
    ('O35018', 26, 153),
    ('O35018', 27, 143),
    ('O35018', 28, 142),
    ('O35018', 29, 132),
    ('O35018', 30, 144);
    
SELECT ID,
       CEILING((SEQ - 1) / 10) + 1 AS SEQ,
       SUM(CASE (SEQ - 1) % 10 WHEN 0 THEN I ELSE 0 END) AS I1,
       SUM(CASE (SEQ - 1) % 10 WHEN 1 THEN I ELSE 0 END) AS I2,
       SUM(CASE (SEQ - 1) % 10 WHEN 2 THEN I ELSE 0 END) AS I3,
       SUM(CASE (SEQ - 1) % 10 WHEN 3 THEN I ELSE 0 END) AS I4,
       SUM(CASE (SEQ - 1) % 10 WHEN 4 THEN I ELSE 0 END) AS I5,
       SUM(CASE (SEQ - 1) % 10 WHEN 5 THEN I ELSE 0 END) AS I6,
       SUM(CASE (SEQ - 1) % 10 WHEN 6 THEN I ELSE 0 END) AS I7,
       SUM(CASE (SEQ - 1) % 10 WHEN 7 THEN I ELSE 0 END) AS I8,
       SUM(CASE (SEQ - 1) % 10 WHEN 8 THEN I ELSE 0 END) AS I9,
       SUM(CASE (SEQ - 1) % 10 WHEN 9 THEN I ELSE 0 END) AS I10
 FROM T1
 GROUP BY ID, CEILING((SEQ - 1) / 10) + 1;

Open in new window

User generated image
»bp
Avatar of vesbos

ASKER

bp!
Thank you so much.
"I" field is varchar type and in case when "I" is like "'0.3775000'" I'm getting error msg.

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '0.3775000' to data type int.

i did
THEN CAST(I as varchar(30)) ELSE 0 END) but still having same error.
Can you share a more representative sample data file I can test with and adjust for ?
Bill has the solution - You just need to add the cast() to it
SELECT ID,
       CEILING((SEQ - 1) / 10) + 1 AS SEQ,
       SUM(CASE (SEQ - 1) % 10 WHEN 0 THEN cast(I as decimal(15, 7)) ELSE 0.0 END) AS I1,
       SUM(CASE (SEQ - 1) % 10 WHEN 1 THEN cast(I as decimal(15, 7)) ELSE 0.0 END) AS I2,
       SUM(CASE (SEQ - 1) % 10 WHEN 2 THEN cast(I as decimal(15, 7)) ELSE 0.0 END) AS I3,
       SUM(CASE (SEQ - 1) % 10 WHEN 3 THEN cast(I as decimal(15, 7)) ELSE 0.0 END) AS I4,
       SUM(CASE (SEQ - 1) % 10 WHEN 4 THEN cast(I as decimal(15, 7)) ELSE 0.0 END) AS I5,
       SUM(CASE (SEQ - 1) % 10 WHEN 5 THEN cast(I as decimal(15, 7)) ELSE 0.0 END) AS I6,
       SUM(CASE (SEQ - 1) % 10 WHEN 6 THEN cast(I as decimal(15, 7)) ELSE 0.0 END) AS I7,
       SUM(CASE (SEQ - 1) % 10 WHEN 7 THEN cast(I as decimal(15, 7)) ELSE 0.0 END) AS I8,
       SUM(CASE (SEQ - 1) % 10 WHEN 8 THEN cast(I as decimal(15, 7)) ELSE 0.0 END) AS I9,
       SUM(CASE (SEQ - 1) % 10 WHEN 9 THEN cast(I as decimal(15, 7)) ELSE 0.0 END) AS I10
 FROM T1
 GROUP BY ID, CEILING((SEQ - 1) / 10) + 1;

Open in new window

Avatar of vesbos

ASKER

here is more sample data.
I prefer to use datatype varchar for I field .

ID       Seq         I
I05649   1   0.3775000
I05649   2   0.3788000
I05649   3   0.3819000
I05649   4   0.3850000
I05649   5   0.3788000
I05649   6   0.3819000
I05649   7   0.3788000
I05649   8   0.3800000
I05649   9   0.3781000
I05649   10   0.3781000
I05649   11   0.3825000
I05649   12   0.3800000
I05649   13   0.3788000
I05649   14   0.3788000
I05649   15   0.3781000
I05649   16   0.3794000
I05649   17   0.3838000
I05649   18   0.3794000
I05649   19   0.3769000
I05649   20   0.3800000
I05649   21   0.3781000
I05649   22   0.3806000
I05649   23   0.3838000
I05649   24   0.3806000
I05649   25   0.3769000
I05649   26   0.3838000
I05649   27   0.3750000
ASKER CERTIFIED SOLUTION
Avatar of Bill Prew
Bill Prew

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of vesbos

ASKER

Bill Prew it is an awesome solution!
Way to go and thank you so much! I was  all blown away.
Great, glad that was useful!