May 23, 2019 at 3:54 pm
Hello,
Below is my sample data
with data as (
select 1 as ID,'Apple' as ProductName,'1' as serving, 'g' as unit union all
select 1 as ID,'Orange' as ProductName,'2' as serving, 'mg' as unit union all
select 1 as ID,'Pinapple' as ProductName,'1' as serving, 'g' as unit union all
select 2 as ID,'Apple' as ProductName,'1' as serving, 'g' as unit union all
select 2 as ID,'Orange' as ProductName,'2' as serving, 'mg' as unit union all
select 2 as ID,'Pinapple' as ProductName,'1' as serving, 'g' as unit
)
Expected Output :
select 1 as Id, 1 as Apple,'g' as Unit , 2 as Orange,'mg' as unit, 1 as Pinapple,'g' as Unit union all
select 2, 1 as Apple,'g' as Unit, 2 as Orange,'mg' as Unit , 1 as Pinapple,'g' as Unit
The requirement here is, group the product id and transpose the product name value as column name and serving value as row value and unit colum name and unit value as value..
Is this possible to do with PIVOT? if its direct transpose,i know the pivot. but this requirement is little weird. any sample query will be highly appreciated. Thanks in advance.
May 23, 2019 at 5:35 pm
;with data as (
select 1 as ID,'Apple' as ProductName,'1' as serving, 'g' as unit union all
select 1 as ID,'Orange' as ProductName,'2' as serving, 'mg' as unit union all
select 1 as ID,'Pineapple' as ProductName,'1' as serving, 'g' as unit union all
select 2 as ID,'Apple' as ProductName,'1' as serving, 'g' as unit union all
select 2 as ID,'Orange' as ProductName,'2' as serving, 'mg' as unit union all
select 2 as ID,'Pineapple' as ProductName,'1' as serving, 'g' as unit
)
SELECT
ID,
MAX(CASE WHEN ProductName = 'Apple' THEN serving END) AS Apple,
MAX(CASE WHEN ProductName = 'Apple' THEN unit END) AS Apple_Unit,
MAX(CASE WHEN ProductName = 'Orange' THEN serving END) AS Orange,
MAX(CASE WHEN ProductName = 'Orange' THEN unit END) AS Orange_Unit,
MAX(CASE WHEN ProductName = 'Pineapple' THEN serving END) AS Pineapple,
MAX(CASE WHEN ProductName = 'Pineapple' THEN unit END) AS Pineapple_Unit
FROM data
GROUP BY ID
ORDER BY ID
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 23, 2019 at 6:30 pm
Thanks a lot scott.
May 23, 2019 at 7:01 pm
A PIVOT can only work on one source column, but your data is coming from two columns. This is why the CROSS TAB approach is usually better. There's a similar issue with UNPIVOT.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply