CODEWITHSUNDEEP
sql-server
Chrislaar123
Chrislaar123

Reputation: 321

Pivot query without knowing the number of columns needed

I have a query that returns the following data.

ProductCode DealRef
1120          23
1120          76
1130          24

Is there a way that if a product code has more than one Deal ref then it will put this into a new column? So the current result would look something like;

ProductCode  Deal1   Deal2
1120          23       76
1130          24

If this is not possible then I have an idea that could work. I would do a count on the DealRef column to find out many columns i would need to pivot to. I would then need to add another column to my initial query which will be able to add an id to each row displaying something similar to the below which I'm unsure how to do.

ProductCode DealRef  id
1120          23      1
1120          76      2
1130          24      1

Upvotes: 1

Views: 62

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

You cannot get the fitting number of columns, but you can get as many columns as you expect to be the maximum, most of them beeing NULL:

Paste this into an empty query window and execute. Adapt to your needs

DECLARE @tbl TABLE(ProductCode INT, DealRef INT);
INSERT INTO @tbl VALUES
 (1120,23)
,(1120,76)
,(1130,24);

SELECT p.*
FROM
(
    SELECT 'deal' + CAST(ROW_NUMBER() OVER(PARTITION BY tbl.ProductCode ORDER BY tbl.ProductCode) AS VARCHAR(10)) AS ColumnName
          ,tbl.ProductCode
          ,tbl.DealRef
    FROM @tbl AS tbl
) AS x
PIVOT
(
    MIN(DealRef) FOR ColumnName IN(deal1,deal2,deal3,deal4 /*Add as many Col-names as you could maximum need*/)
) AS p

Result is

ProductCode deal1   deal2   deal3   deal4
1120        23      76      NULL    NULL
1130        24      NULL    NULL    NULL

Upvotes: 2

Related Questions