SQL return multiple values in rows into columns

Question

I have a table with a few columns, but only two are relevant to this search:

ITEMNMBR and ALTITEM1

Right now data appears as follows

ITEMNMBR    ALTITEM1
A0001       ABCDEF
A0001       GBESJF
A0001       SDFJLK
B0001       JKHKJF
C0001       KLJSDF
C0001       MSXNCV

I would like to display the data so only one instance of each ITEMNMBR appears in the column with the corresponding ALTITEM1 values appearing in new columns.

ITEMNMBR    ALTITEM_1    ALTITEM_2    ALTITEM_3
A0001       ABCDEF       GBESJF       SDFJLK
B0001       JKHKJF
C0001       KLJSDF       MSXNCV

Each ITEMNMBR may have up to 20 ALTITEM1 values. If possible to add these columns dynamically that's great, but I figure that they need to be provisioned in the code...

SQL server 2008 R2

Search for `pivot`, lots of good posts out there. – Andrew Jun 24 '15 at 22:08 — Andrew, Jun 24 '15 at 22:08

score 0 · Answer 1 · answered Jun 24 '15 at 21:00

    (select itemnmber,
    case when altitem1 as altitem_1,
    null as altitem_2,
    null as altitem_3
    --repeat this until you reach 20 items
    from tablename)
    union all
    (select itemnmber,
    null as altitem_1,
    altitem1 as altitem_2,
    null as altitem_3
    --repeat this until you reach 20 items
    from tablename) 
     union all
    (select itemnmber,
    null as altitem_1,
    null as altitem_2,
    altitem1 as altitem_3,
    --repeat this until you reach 20 items
    from tablename)
    ---repeat the whole process as many times with the column names

You can also use a empty string instead of null

score 0 · Accepted Answer · edited May 23 '17 at 11:58

You can simply use a PIVOT function for this like the following:

-- Create demo data
CREATE TABLE #temp(ITEMNMBR nvarchar(20), ALTITEM1 nvarchar(20))

INSERT INTO #temp(ITEMNMBR, ALTITEM1)
VALUES  (N'A0001',N'ABCDEF'),(N'A0001',N'GBESJF'),
        (N'A0001',N'SDFJLK'),(N'B0001',N'JKHKJF'),
        (N'C0001',N'KLJSDF'),(N'C0001',N'MSXNCV')

SELECT *
FROM (
    SELECT ITEMNMBR, ALTITEM1, ROW_NUMBER() OVER(PARTITION BY ITEMNMBR ORDER BY ALTITEM1) as rowNo
    FROM #temp
    ) as data
PIVOT(
    MAX(ALTITEM1)
    FOR rowNo IN([1],[2],[3])
) as pvt

-- Cleanup
DROP TABLE #temp

If you need a more dynamic approach (which won't work in a view), you can also take a look at my other solution here, which will dynamically create the IN()-clause here.

This worked perfectly. Thank you! – TNFLD Jun 25 '15 at 13:21 — TNFLD, Jun 25 '15 at 13:21

SQL return multiple values in rows into columns

2 Answers2