PIVOT and UNPIVOT? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

PIVOT and UNPIVOT?

Author

Topic

sql_server_dba
Posting Yak Master

167 Posts

Posted - 2013-06-03 : 18:16:21

i have 3 tables and i believe the output which i am looking for can be done by using PIVOT or UNPIVOT. Can anyone help please?

DECLARE @Product TABLE (Product_ID INT, Version INT)

INSERT INTO @Product VALUES (111,1)
INSERT INTO @Product VALUES (111,2)
INSERT INTO @Product VALUES (111,3)

SELECT *
FROM @Product

DECLARE @ProductRelatedFields TABLE (Product_ID INT, Version INT, ProductRelatedID INT, ProductRelatedValue VARCHAR(20) )

INSERT INTO @ProductRelatedFields VALUES (111, 1,1,'$100')
INSERT INTO @ProductRelatedFields VALUES (111, 1,2,'Wrist Band')
INSERT INTO @ProductRelatedFields VALUES (111, 1,3,'ABCD')
INSERT INTO @ProductRelatedFields VALUES (111, 2,1,'$100')
INSERT INTO @ProductRelatedFields VALUES (111, 2,2,'Head band')
INSERT INTO @ProductRelatedFields VALUES (111, 2,3,'ABCD')
INSERT INTO @ProductRelatedFields VALUES (111, 3,1,'$100')
INSERT INTO @ProductRelatedFields VALUES (111, 3,2,'Band')
INSERT INTO @ProductRelatedFields VALUES (111, 3,3,'ABCD')

DECLARE @ProductRelatedTypes TABLE (ProduceRelatedID INT, ProductRelateName VARCHAR(20) )

INSERT INTO @ProductRelatedTypes VALUES(1, 'Price')
INSERT INTO @ProductRelatedTypes VALUES(1, 'Item')
INSERT INTO @ProductRelatedTypes VALUES(1, 'Company')

-- Output should be...

DECLARE @OutPut TABLE (Product_ID INT, Version INT, ProductRelatedID INT, Price VARCHAR(20), Item VARCHAR(20), Company VARCHAR(20))

INSERT INTO @OutPut VALUES(111, 1, 1, '$100', 'Wrist Band', 'ABCD')
INSERT INTO @OutPut VALUES(111, 2, 2, '$100', 'Head Band', 'ABCD')
INSERT INTO @OutPut VALUES(111, 3, 3, '$100', 'Band', 'ABCD')

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-06-03 : 23:18:13

why do you have the "ProductRelatedI " in your output ? It does not make sense there

select	*
from	(
		select	p.Product_ID, p.Version, f.ProductRelatedValue, t.ProductRelateName
		from	@Product p
			inner join @ProductRelatedFields f	on	p.Product_ID		= f.Product_ID
								and	p.Version		= f.Version
			inner join @ProductRelatedTypes t	on	f.ProductRelatedID	= t.ProduceRelatedID
	) d
	pivot
	(
		max(ProductRelatedValue)
		for ProductRelateName in ([Price], [Item], [Company])
	) pv

KH
[spoiler]Time is always against us[/spoiler]

sql_server_dba
Posting Yak Master

167 Posts

Posted - 2013-06-04 : 17:36:51

Thanks for your help but the above query gives the output incorrectly. Maybe PIVOT or UNPIVOT are not the ones that should be used here. Maybe something else.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-04 : 17:55:24

[code]SELECT * FROM (
SELECT p.Product_ID, p.Version,pr.ProductRelatedID, pr.ProductRelatedValue FROM
@Product p
LEFT JOIN @ProductRelatedFields pr ON pr.Product_ID = p.Product_ID AND pr.Version = p.Version
) s
PIVOT (MAX(ProductRelatedValue) FOR ProductRelatedID IN ([1],[2],[3]))P[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-05 : 00:15:28

quote:
Originally posted by sql_server_dba

Thanks for your help but the above query gives the output incorrectly. Maybe PIVOT or UNPIVOT are not the ones that should be used here. Maybe something else.

Can you show the current output vs expected one? I think Tans suggestion should give you waht you're after except for ProductRelatedID which i agree doesnt make any sense to given output

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-05 : 08:14:29

quote:
Originally posted by visakh16

quote:
Originally posted by sql_server_dba

Thanks for your help but the above query gives the output incorrectly. Maybe PIVOT or UNPIVOT are not the ones that should be used here. Maybe something else.

Can you show the current output vs expected one? I think Tans suggestion should give you waht you're after except for ProductRelatedID which i agree doesnt make any sense to given output

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Copy the last part of sql_server_dba's original posting (reproduced below) to an SSMS window and run it, and it will show you his expected output.

The ProductRelatedID does not make sense in the output, but for that, the output does seem reasonable.

And, no Tan's query does not give the right results, it gives something completely different, showing the same "$100" in 3 rows of three columns (Price, Item and Company).

quote:
-- Output should be...

DECLARE @OutPut TABLE (Product_ID INT, Version INT, ProductRelatedID INT, Price VARCHAR(20), Item VARCHAR(20), Company VARCHAR(20))

INSERT INTO @OutPut VALUES(111, 1, 1, '$100', 'Wrist Band', 'ABCD')
INSERT INTO @OutPut VALUES(111, 2, 2, '$100', 'Head Band', 'ABCD')
INSERT INTO @OutPut VALUES(111, 3, 3, '$100', 'Band', 'ABCD')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources