SQL Server change column format to rows

Question

My DB records are in column format:

ingredient    product
Ing1            AAA
Ing1            BBB
Ing2            AAA
Ing2            BBB
...             ...

I would like see data in format:

product    ingredient1      ingredient2
AAA           Ing1              Ing2
BBB           Ing1              Ing2

select *
from
(
  select product, ingredient
  from db 
) as d
pivot
(
  max(ingredient)
  for ingredient in ( ingredient1 , ingredient2)
) piv

But this query return empty ingredient1 and ingredient2.

There can be only `Ing1` and `Ing2` as ingridients? Or It can be `Ing3`? All of ingridients end with number? `Ing1 = ingridient1`, `Ing2 = ingridient2`? — Stanislovas Kalašnikovas, Jul 17 '15 at 12:02
possible duplicate of [SQL Server dynamic PIVOT query?](http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — Carsten Massmann, Jul 17 '15 at 12:20

score 0 · Answer 1 · answered Jul 17 '15 at 11:47

0

I suggest you to use this type of query:

SELECT
    product,
    MAX(CASE WHEN ingredient = 'Ing1' THEN ingredient END) AS ingredient1,
    MAX(CASE WHEN ingredient = 'Ing2' THEN ingredient END) AS ingredient2
FROM 
    db
GROUP BY
    product;

answered Jul 17 '15 at 11:47

shA.t

16,580
5
54
111

'Ing1' and 'Ing2' can't be static values – Klapsius Jul 17 '15 at 11:51
@Klapsius And how you choose an `ingredient` to come below `ingredient1` or `ingredient2`? – shA.t Jul 17 '15 at 11:54
I change ingredient=ingredient then returns save value into both culumns – Klapsius Jul 17 '15 at 11:58
@Klapsius You should be more specific on this ;). – shA.t Jul 17 '15 at 12:06

Stanislovas Kalašnikovas · Answer 2 · 2015-07-17T12:13:19.677

0

In PIVOT IN([Ing1], [Ing2]) you should use original names of values, later you can alias them in following:

SELECT product, 
       [Ing1] as [Ingridient1], 
       [Ing2] as [Ingridient2]
FROM
(
  SELECT product, ingredient
  FROM #t1 
) AS d
PIVOT
(
  MAX(ingredient)
  FOR ingredient IN ([Ing1], [Ing2])
) piv

You can check working example at SQL FIDDLE

edited Jul 17 '15 at 12:13

answered Jul 17 '15 at 12:07

Stanislovas Kalašnikovas

9,203
4
32
59

return Ingredient1 and Ingredient2 empty – Klapsius Jul 17 '15 at 12:20
Check sql fiddle, It's returning as expected, you do something wrong http://sqlfiddle.com/#!6/f5e44/2 – Stanislovas Kalašnikovas Jul 17 '15 at 12:21
Same returns empty values. – Klapsius Jul 17 '15 at 12:30

score 0 · Answer 3 · answered Jul 17 '15 at 12:17

You can write a dynamic pivot as:

declare @Finalcollist varchar(max), @collist varchar(max)
declare @sql nvarchar(max)

select @Finalcollist = COALESCE(@Finalcollist + ', ','') 
                      + QUOTENAME(ingredient) +' AS '
                      + Replace (QUOTENAME(ingredient),'Ing','Ingredient')
from 
( SELECT DISTINCT ingredient FROM test1) T (ingredient)


select @collist = COALESCE(@collist + ', ','') 
                      + QUOTENAME(ingredient)                     
from 
( SELECT DISTINCT ingredient FROM test1) T (ingredient)

select @Finalcollist,@collist -- This givesdynamic column list

-- Now setting this @Finalcollist variable in the Dynamic SQL.
set @sql = '
select product, ' + @Finalcollist + '
from 
(select product,ingredient from test1
 )p
PIVOT(MAX (ingredient) FOR ingredient IN ( ' + @collist + ' )
) AS pvt
ORDER BY product'

--print @sql

EXECUTE sp_executesql @sql

DEMO

score 0 · Answer 4 · answered Jul 17 '15 at 12:23

Here some fun tryout with which you can have as many Ing's and it will increase the number of columns.

CREATE TABLE #temp(ing nvarchar(5), pr nvarchar(5))
INSERT INTO #temp values ('Ing1','AA')
INSERT INTO #temp values ('Ing1','BB')
INSERT INTO #temp values ('Ing2','AA')
INSERT INTO #temp values ('Ing2','BB')
INSERT INTO #temp values ('Ing3','AA')

DECLARE @PivotCols VARCHAR(MAX);
SELECT @PivotCols = COALESCE(@PivotCols + ',','') + QUOTENAME(ing) FROM #temp GROUP BY ing ORDER BY ing
EXEC
(
'SELECT * FROM #temp PIVOT
(
    MAX(ing) FOR ing IN (' + @PivotCols + ')
) pvt
')

DROP TABLE #temp

This will result as;

pr    Ing1  Ing2  Ing3
----- ----- ----- -----
AA    Ing1  Ing2  Ing3
BB    Ing1  Ing2  NULL

SQL Server change column format to rows

4 Answers4