Convert Rows to columns with variable number

user_4190226 0
Viewed 39

I have read stuff on pivot tables and I am still having problems getting this correct.

I have a table where column 1 is a Productnr, and column 2 is LHS, column 3 is another Productnr and lastly column 4 is RHS. Also the number of entries of Productnr2 (belonging to Productnr) is variable between 1 and 5. For example, if there are only 3 associated Productnr2 (see article no = 060013 in Productnr) then the last two columns should remain empty.

Productnr  LHS  Productnr2    RHS
060009     411  099088        5
060013     228  194139        25
060013     228  194141        17
060013     228  175823        75
060022     951  147071        90
060034     424  099088        14
060034     424  102704        88
060034     424  080034        82
060034     424  108436        87
060034     424  108437        58

I would like it to come out as a pivot table, like this:

Productnr |  LHS |  1  |  RHS  |  2  |  RHS  |  3  |  RHS  |  4  |  RHS  |  5  |  RHS  |   
----- 
060009       411  099088   5    
060013       228  194139   25   194141   17   175823   75
060022       951  147071   90
060034       424  099088   14   102704   88   080034   82   108436   87   108437   58
sql-server sql
1 Answers

This can achieve by the following query. But this will work only for five different columns only, if more columns needed then need to increase the case count, but this is not recommended. Better try some dynamic queries for more columns.

CREATE TABLE #temp(Productnr INT, LHS INT, Productnr2 INT, RHS INT)

INSERT INTO #temp VALUES (060009,411,099088,5 )
INSERT INTO #temp VALUES (060013,228,194139,25)
INSERT INTO #temp VALUES (060013,228,194141,17)
INSERT INTO #temp VALUES (060013,228,175823,75)
INSERT INTO #temp VALUES (060022,951,147071,90)
INSERT INTO #temp VALUES (060034,424,099088,14)
INSERT INTO #temp VALUES (060034,424,102704,88)
INSERT INTO #temp VALUES (060034,424,080034,82)
INSERT INTO #temp VALUES (060034,424,108436,87)
INSERT INTO #temp VALUES (060034,424,108437,58)

;WITH CTE AS(
    SELECT *
        ,ROW_NUMBER() OVER(PARTITION BY Productnr ORDER BY Productnr)RN 
    FROM #temp
    )
SELECT Productnr,LHS
    ,MAX(CASE WHEN RN=1 THEN Productnr2 END) [1]
    ,MAX(CASE WHEN RN=1 THEN RHS END) [RHS]
    ,MAX(CASE WHEN RN=2 THEN Productnr2 END) [2]
    ,MAX(CASE WHEN RN=2 THEN RHS END) [RHS]
    ,MAX(CASE WHEN RN=3 THEN Productnr2 END) [3]
    ,MAX(CASE WHEN RN=3 THEN RHS END) [RHS]
    ,MAX(CASE WHEN RN=4 THEN Productnr2 END) [4]
    ,MAX(CASE WHEN RN=4 THEN RHS END) [RHS]
    ,MAX(CASE WHEN RN=5 THEN Productnr2 END) [5]
    ,MAX(CASE WHEN RN=5 THEN RHS END) [RHS]
FROM CTE
GROUP BY Productnr,LHS

DROP TABLE #temp

Output:

Productnr   LHS         1           RHS         2           RHS         3           RHS         4           RHS         5           RHS
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
60009       411         99088       5           NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL
60013       228         194139      25          194141      17          175823      75          NULL        NULL        NULL        NULL
60022       951         147071      90          NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL
60034       424         99088       14          102704      88          80034       82          108436      87          108437      58
user_38765650
Related