CODEWITHSUNDEEP
sqlsql-serversql-server-2014
VSDekar
VSDekar

Reputation: 1821

Multiple Records to one Row

I need to transform this given table:

ArticleId  | SymbolId
-----------+----------
1          | 1
1          | 2
1          | 3
2          | 6
2          | 7
2          | 3
2          | 4
2          | 5

into another table like this:

ArticleId  |  Symbol1  | Symbol2  | Symbol3  | Symbol4  | Symbol5
-----------+-----------+----------+----------+----------+---------
1          |  1        | 2        | 3        | NULL     | NULL
2          |  6        | 7        | 3        | 4        | 5

Is there an easy way to do this? There are always max 5 records per ArticleId. No special order is required.

Upvotes: 0

Views: 43

Answers (1)

Felix Pamittan
Felix Pamittan

Reputation: 31879

You could use MAX(CASE WHEN END) :

;WITH YourTable(ArticleId, SymbolId) AS(
    SELECT 1, 1 UNION ALL
    SELECT 1, 2 UNION ALL
    SELECT 1, 3 UNION ALL
    SELECT 2, 6 UNION ALL
    SELECT 2, 7 UNION ALL
    SELECT 2, 3 UNION ALL
    SELECT 2, 4 UNION ALL
    SELECT 2, 5 
)
SELECT
    ArticleId,
    Symbol1 = MAX(CASE WHEN RN = 1 THEN SymbolId END),
    Symbol2 = MAX(CASE WHEN RN = 2 THEN SymbolId END),
    Symbol3 = MAX(CASE WHEN RN = 3 THEN SymbolId END),
    Symbol4 = MAX(CASE WHEN RN = 4 THEN SymbolId END),
    Symbol5 = MAX(CASE WHEN RN = 5 THEN SymbolId END)
FROM(
    SELECT *,
        RN = ROW_NUMBER() OVER(PARTITION BY ArticleId ORDER BY (SELECT NULL))
    FROM YourTable
)t
GROUP BY ArticleId

Using PIVOT

;WITH YourTable(ArticleId, SymbolId) AS(
    SELECT 1, 1 UNION ALL
    SELECT 1, 2 UNION ALL
    SELECT 1, 3 UNION ALL
    SELECT 2, 6 UNION ALL
    SELECT 2, 7 UNION ALL
    SELECT 2, 3 UNION ALL
    SELECT 2, 4 UNION ALL
    SELECT 2, 5 
)
SELECT
    ArticleId,
    Symbol1 = [1], 
    Symbol2 = [2], 
    Symbol3 = [3], 
    Symbol4 = [4], 
    Symbol5 = [5]
FROM (
    SELECT *,
        RN = ROW_NUMBER() OVER(PARTITION BY ArticleId ORDER BY (SELECT NULL))
    FROM YourTable
)t
PIVOT (
    MAX(t.SymbolId)
    FOR RN IN ([1], [2], [3], [4], [5])
)p

Upvotes: 2

Related Questions