CODEWITHSUNDEEP
sqlsql-serverpivot
kolinunlt
kolinunlt

Reputation: 345

Multiple rows using pivot in sql

I have a table.

Item  | ID
--------------
1-A   | 213
2-B   | 432
3-C   | 267
3-C   | 879
3-C   | 467

I use pivot.

Select * from (
    Select ID,Item
    From data1
)R
Pivot (
    Max(ID) For Item 
    in 
    ([1-A],[2-B],[3-C])
)as pvt

But I can only get one value.

1-A  | 2-B | 3-C
-----------------
213  | 453 | 467

How can I get all the values of 3-C, I hope I can get this result.

1-A  | 2-B | 3-C | 3-C | 3-C
---------------------------
213  | 453 | 267 | 879 | 467

But 3-C, there may be one or more, I cannot control how many will appear.

Hope to help answer, thank you.

Upvotes: 0

Views: 83

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

An alternative method would be to put the results in a single column for 3-C values, using string_agg():

select string_agg(case when item = '[1-A]' then id end, ',') within group (order by id) as id_1_a,
       string_agg(case when item = '[2-B]' then id end, ',') within group (order by id) as id_2_b,
       string_agg(case when item = '[3-C]' then id end, ',') within group (order by id) as id_3_c
from data1;

This is not the exact result set you specify, but it might accomplish what you need.

Upvotes: 0

Thom A
Thom A

Reputation: 95557

The real problem here is that you have an indeterminable number of rows. This means you need dynamic SQL, The next problem is that you don't have unique values to pivot on, so we need to get inventive with ROW_NUMBER as well. This gives this, which is not particularly pretty, but "works".

CREATE TABLE dbo.YourTable (Item char(3),
                            ID int);
INSERT INTO dbo.YourTable (Item,
                           ID)
VALUES('1-A',213),
      ('2-B',432),
      ('3-C',267),
      ('3-C',879),
      ('3-C',467);
GO

DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13) + NCHAR(10);

DECLARE @Delimiter nvarchar(30) = N',' + @CRLF + N'       ';

WITH RNs AS(
    SELECT Item,
           ID,
           ROW_NUMBER() OVER (ORDER BY Item) AS RN
    FROM dbo.YourTable)
SELECT @SQL = N'WITH RNs AS(' + @CRLF +
              N'    SELECT Item,' + @CRLF +
              N'           ID,' + @CRLF +
              N'           ROW_NUMBER() OVER (ORDER BY Item) AS RN' + @CRLF +
              N'    FROM dbo.YourTable)' + @CRLF +
              N'SELECT ' +
              STRING_AGG(CONCAT(N'MAX(CASE WHEN Item = ',QUOTENAME(R.Item,''''),N' AND RN = ',R.RN,N' THEN R.ID END) AS ',QUOTENAME(R.Item)),@Delimiter) WITHIN GROUP (ORDER BY R.Item, R.RN) + @CRLF + 
              N'FROM RNs R;'
FROM RNs R;

PRINT @SQL;
EXEC sys.sp_executesql @SQL;

GO

DROP TABLE dbo.YourTable;

I have assumed you are using a recent version of SQL Server. If not, you will need to replace STRING_AGG with the older FOR XML PATH method.

DB<>Fiddle

Upvotes: 3

Squirrel
Squirrel

Reputation: 24763

You will need to use Dynamic SQL since you have an unknown number of columns. The query is form dynamically and use sp_executesql to execute the query

Looks like you wanted a distinct value of Item & ID combination, so I use GROUP BY Item, ID here.

declare @sql nvarchar(max)

SELECT @sql = isnull(@sql + ',', 'SELECT ')
            + CONVERT(VARCHAR(10), ID) + ' AS ' + QUOTENAME(Item)
FROM   data1
GROUP BY Item, ID
ORDER BY Item, ID

-- PRINT @sql
exec sp_executesql @sql

Example : dbfiddle

Upvotes: 0

Related Questions