Display table in different format

Question

i have the sql code that returns the result

Set1   Value
A       A1      
A       A2
A       A3
A       A4

How do i display the result like this

Set1    Value1      Value2       Value3      Value4
A           A1        A2           A3          A4

Thanks

You may be able to achieve this with Common Table Expression. I am not sure though. What would you need this behavior for? — jworrin, Jul 06 '11 at 03:20
Please tag and/or specify the version of SQL Server you are using. In 2005+ you can use PIVOT but there's little sense coding a solution if it's not going to be valid. — Aaron Bertrand, Jul 06 '11 at 03:43

score 2 · Answer 1 · edited May 23 '17 at 10:24

2

take a look at this

SQL Server: Examples of PIVOTing String data

edited May 23 '17 at 10:24

Community

1
1

answered Jul 06 '11 at 04:16

Binil

6,445
3
30
40

Lieven Keersmaekers · Answer 2 · 2011-07-06T06:27:21.080

Following would do the trick. Using ROW_NUMBER allows for values other than A1-A4

SQL Statement

SELECT  [Set1] = Set1
        , [Value1] = MIN(CASE WHEN rn = 1 THEN Value END)
        , [Value2] = MIN(CASE WHEN rn = 2 THEN Value END)
        , [Value3] = MIN(CASE WHEN rn = 3 THEN Value END)
        , [Value4] = MIN(CASE WHEN rn = 4 THEN Value END)
FROM    (       
            SELECT  Set1
                    , Value
                    , rn = ROW_NUMBER() OVER (PARTITION BY Set1 ORDER BY Value)
            FROM    AResult
        ) q
GROUP BY
        Set1

Display table in different format

2 Answers2

SQL Statement