0

I need to convert a row to a column, I never worked on in this scenario.

Product

 ProdID   Price
 ---------------
 111      52.5
 111      50.5
 112      40
 111      65

Expected results:

ProdID    Price1    Price2    Price3
------------------------------------
111       52.5      50.5     65
112       40

Note

I have no idea how many prices will be there for the same item. Sometimes it will be only one, sometimes 2 or 5.

So based on that it has to create the column.

I saw a lot of posts only converting the exact column, not for a dynamic column like my scenario.

Nguyễn Văn Phong
  • 13,506
  • 17
  • 39
  • 56
Liam neesan
  • 2,282
  • 6
  • 33
  • 72
  • 1
    You'll need a dynamic SQL query similar to that described [here](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) – Nick Feb 24 '20 at 05:28
  • 1
    @Nick In my case, the column name will be `Price1, Price2, Price3`. Not static column – Liam neesan Feb 24 '20 at 05:36
  • 1
    You could get close with `GROUP_CONCAT`. For example: `SELECT ProdID, Prices AS GROUP_CONCAT(Price, ',') FROM Product GROUP BY ProdID` – charles-allen Feb 24 '20 at 05:40
  • 1
    @AjahnCharles I search about `GROUP_CONCAT`. I don't want like that. Your result will show in single column. But this is good thing that I learn about `Group_concat` Thanks – Liam neesan Feb 24 '20 at 05:45
  • 2
    @Liamneesan `GROUP_CONCAT` is MySQL, not SQL Server. – Nick Feb 24 '20 at 05:49
  • I've just given [the answer](https://stackoverflow.com/a/60370238/9071943), please take a look at @Nick – Nguyễn Văn Phong Feb 24 '20 at 05:58
  • The SQL language has a _very strict rule:_ know the number and data type for the columns at query build time, _before_ looking at any data. Even `SELECT *` queries meet this rule because column list described by `*` is known and static. What you're asking for now _breaks the rule._ The best option usually pivots in the client, but if you _realy_ must, you need three steps: **1)** Run a query to look at the data tell you how many columns you'll need. **2)** Use those results to a build a new query on the fly, which a separate entry for each column from step 1. **3)** Run the query from step 2. – Joel Coehoorn Feb 24 '20 at 06:10

2 Answers2

1

Demo on db<>fiddle

  1. You need to use Dynamic SQL PIVOT like https://stackoverflow.com/a/60331153
  2. Use ROW_NUMBER() combines with CONCAT() to mark dynamic column like Price1, Price2, Price3, etc.
    DECLARE 
        @columns NVARCHAR(MAX) = '',
        @sql     NVARCHAR(MAX) = '';


     SELECT ProdID, Price, Col = CONCAT('Price', ROW_NUMBER() OVER (PARTITION BY ProdID ORDER BY ProdID))
     into #b
     FROM #a

    SELECT @columns += QUOTENAME(Col) + ','
    from (SELECT DISTINCT Col FROM #b) A

    -- remove the last comma
    SET @columns = LEFT(@columns, LEN(@columns) - 1);


    SET @sql = 'SELECT * FROM ( SELECT ProdID, Price, Col FROM  #b) src PIVOT( MAX([Price]) FOR Col IN ('+ @columns +')) AS pivot_table;';

    -- execute the dynamic SQL
    EXECUTE sp_executesql @sql;

Output

enter image description here

Nguyễn Văn Phong
  • 13,506
  • 17
  • 39
  • 56
0
    Create table ItemDetails
(
    prodid numeric(10),
    price numeric(10,2),
    orderno int identity(1,1)
)

declare @pricecols varchar(max)='',
@pricecolName nvarchar(max)='',
@statement nvarchar(4000)=''

 select @pricecolName= stuff( 
            (
                select ','+pricecol from (
             select  distinct 'price'+convert(varchar(10),(row_number() over(partition by prodid order by orderno))) 
            pricecol FROM ItemDetails   ) as tbl
            for xml path('')),1,1,'')


set @statement= 'select prodID,'+@pricecolName+' from (
    select  prodid,price,''price''+convert(varchar(10),(row_number() 
    over(partition by prodid order by orderno))) as
            pricecol FROM ItemDetails  
) [itemPrice]
pivot
(
    max(price)
    for pricecol
    in ('+@pricecolName+') ) as pivatetble'

 exec sp_executesql  @statement

OutPut- (note: price value duplicated for Item2 [112])1

Balamurugan Annamalai
  • 36
  • 2