x

T-Sql Query

Hi can any one give alternate solution for the result set i am getting without using dynamic T-Sql

 CREATE TABLE #PVO (PVOID uniqueidentifier,ProductID uniqueidentifier,POID uniqueidentifier)
  INSERT INTO #PVO (PVOID,ProductID,POID)
  VALUES(
 
  'AEA42FF7-A98E-46C5-AC92-319783132435','838608F8-92FB-4EB5-81DF-191247C52AC8', 'EB28E584-D4D1-40FD-8879-53D527F7CA22'),
 ('9D00524F-740A-428C-AFA5-35D15C4EA92A' ,'838608F8-92FB-4EB5-81DF-191247C52AC8', 'B7E699FD-362B-4D0D-9DCD-61E5EDF17370'),
 ('BFF8DE6B-D236-4489-B6DE-624E92AD48DF' ,'838608F8-92FB-4EB5-81DF-191247C52AC8' ,'0C370920-2A8B-4EA9-B817-B38852E72DDC'),
 ('1805BA8D-A447-4B37-98B9-3B4A104BFC0E' ,'B2E4E299-C24D-46DB-A60B-322FA5F13D15' ,'B7E699FD-362B-4D0D-9DCD-61E5EDF17370'),
 ('FE451E39-590F-4975-B572-419A6D36D7D0' ,'B2E4E299-C24D-46DB-A60B-322FA5F13D15' ,'7914F307-8FF4-4569-93A8-14CDD69A6627'),
 ('D43939FB-9922-497D-992A-CF5EBC797C1E' ,'B2E4E299-C24D-46DB-A60B-322FA5F13D15' ,'A9996CA8-C420-4CD5-A793-0543193BDEED'),
 ('83434026-1FCB-4059-B183-023A2E680D2A' ,'A25FDE2E-309A-4711-AD9A-807E207A6940' ,'0C370920-2A8B-4EA9-B817-B38852E72DDC'),
 ('14FA240A-0F13-435B-BB7F-3AD37992AE11' ,'A25FDE2E-309A-4711-AD9A-807E207A6940' ,'4A2924EF-1892-4DA3-95CE-6747BD13E2F5'),
 ('4AC1EE54-D68D-4241-8156-D56B4C7218EC' ,'A25FDE2E-309A-4711-AD9A-807E207A6940' ,'A9996CA8-C420-4CD5-A793-0543193BDEED'),
 ('B41B9457-29B7-4B1F-856F-04AA8D36A513' ,'0384E68A-54BB-4124-B0D8-974C97114F40', '84114D41-2299-4221-9254-700DD09DC401'),
 ('CE7FC25F-9C1E-4059-A985-5A7F5AC48B05' ,'0384E68A-54BB-4124-B0D8-974C97114F40', 'EB28E584-D4D1-40FD-8879-53D527F7CA22'),
 ('8D568985-4191-4621-9791-5EB2FDB1759B' ,'0384E68A-54BB-4124-B0D8-974C97114F40', 'B7E699FD-362B-4D0D-9DCD-61E5EDF17370'),
 ('8B6D8632-29A7-47CD-885A-5E7581453234' ,'DCFC453F-215C-4330-9EF2-A93A79301C6A', '7914F307-8FF4-4569-93A8-14CDD69A6627'),
 ('550E3698-2B81-461F-AA0C-7B575D1B14BF' ,'DCFC453F-215C-4330-9EF2-A93A79301C6A', '46666887-8998-4C38-BB1A-1E62A9926AD6'),
 ('077D5402-7F6B-42B1-8551-C0AA99AC9582' ,'DCFC453F-215C-4330-9EF2-A93A79301C6A', 'B7E699FD-362B-4D0D-9DCD-61E5EDF17370'),
 ('248E9A15-822C-41ED-8352-05EB404DCBF7' ,'4BC947A1-657E-425D-961E-B27507010911', '46666887-8998-4C38-BB1A-1E62A9926AD6'),
 ('DD2B9639-7847-458A-AD88-4A6A09AF4977' ,'4BC947A1-657E-425D-961E-B27507010911', '0C370920-2A8B-4EA9-B817-B38852E72DDC'),
 ('F4E8D538-EBBC-41D2-BC01-BBD1C5C1ACE8' ,'4BC947A1-657E-425D-961E-B27507010911', '4A2924EF-1892-4DA3-95CE-6747BD13E2F5'),
 ('5E19A172-1686-4E62-A1FA-137C5D2ACBB9' ,'D12453F5-FAFA-4DB2-AB79-C65188724886', '4A2924EF-1892-4DA3-95CE-6747BD13E2F5'),
 ('061D3E3A-D9EE-4AC6-B25D-95E3C8D022EE' ,'D12453F5-FAFA-4DB2-AB79-C65188724886', 'A9996CA8-C420-4CD5-A793-0543193BDEED'),
 ('D9B85AE9-937A-4C1B-83E3-E81522DF3974' ,'D12453F5-FAFA-4DB2-AB79-C65188724886', '7914F307-8FF4-4569-93A8-14CDD69A6627'),
 ('99E0976E-4C34-4A3D-9442-31B204DCF522' ,'BE9E01C0-2E3A-4C9D-9B8D-EEC24B7CDD1A', '84114D41-2299-4221-9254-700DD09DC401'),
 ('CE47CBF4-A565-4CB0-A886-885704C5046A' ,'BE9E01C0-2E3A-4C9D-9B8D-EEC24B7CDD1A' ,'46666887-8998-4C38-BB1A-1E62A9926AD6'),
 ('C2F426C0-1FF0-4E98-99BF-B05F0BD23309' ,'BE9E01C0-2E3A-4C9D-9B8D-EEC24B7CDD1A' ,'4A2924EF-1892-4DA3-95CE-6747BD13E2F5')
 
 
 CREATE TABLE #PO  (POID uniqueidentifier,SvID uniqueidentifier,Name varchar(200),Seqno int)
 INSERT INTO #PO
  VALUES('A9996CA8-C420-4CD5-A793-0543193BDEED', '6E1A2601-E0C5-423A-A2CC-7E0954987F5E', 'nylon', 1),
 ('7914F307-8FF4-4569-93A8-14CDD69A6627', '80E701C2-60F9-4F51-920A-03CBCBBB6541', 'Green', 3),
 ('46666887-8998-4C38-BB1A-1E62A9926AD6', '6E1A2601-E0C5-423A-A2CC-7E0954987F5E', 'silk', 13),
 ('EB28E584-D4D1-40FD-8879-53D527F7CA22', '6E1A2601-E0C5-423A-A2CC-7E0954987F5E', 'cotton', 12),
 ('B7E699FD-362B-4D0D-9DCD-61E5EDF17370', '3520E8BE-9A5E-4BFB-9E72-7018DFBCA397', 'small', 25),
 ('4A2924EF-1892-4DA3-95CE-6747BD13E2F5', '3520E8BE-9A5E-4BFB-9E72-7018DFBCA397', 'large', 29),
 ('84114D41-2299-4221-9254-700DD09DC401', '80E701C2-60F9-4F51-920A-03CBCBBB6541', 'grey', 13),
 ('0C370920-2A8B-4EA9-B817-B38852E72DDC', '80E701C2-60F9-4F51-920A-03CBCBBB6541', 'black', 11)
 
 
 
 
 
 CREATE TABLE #SV  (SvID uniqueidentifier,Name varchar(200),Seqno int)
 INSERT INTO #SV
 VALUES('80E701C2-60F9-4F51-920A-03CBCBBB6541', 'Color', 2),
 ('3520E8BE-9A5E-4BFB-9E72-7018DFBCA397' ,'Size', 1),
 ('6E1A2601-E0C5-423A-A2CC-7E0954987F5E', 'fabric', 7)
 
 
 
 
 
 
 
 DECLARE @Column VARCHAR(3000)=(SELECT  DISTINCT NAME+',' FROM #SV FOR XML PATH(''))
 
 SET @Column= substring(@Column,0,len(@Column))
 
 
 DECLARE @ParmDefinition nvarchar(500)=N'@column1 VARCHAR(3000)'
 
 
   
 
 DECLARE @SqlStr NVARCHAR(MAX)=
 N'SELECT *,Poseqno=(SELECT  seqno FROM #PO where POID IN (SELECT  top 1 POID FROM  #PVO WHERE ProductID=pvt.productID))
  ,svseqno=( SELECT top 1 Seqno FROM #SV WHERE svID IN  ((SELECT top 1  svID FROM #PO
  where PoID IN (SELECT  top 1 PoID FROM  #PVO WHERE ProductID=pvt.productID))))
  FROM
 (SELECT    
   VariationName=sv.Name,
   OptionName=po.Name,
   pvm.ProductID
   
 
  
 FROM #PVO pvm
  LEFT JOIN #PO  po on pvm.POID=po.POID
  LEFT JOIN #SV sv on po.SVID=sv.SVID
 
 
 )UP 
 PIVOT(MAX(OptionName) FOR VariationName in ('+@Column+') ) AS PVT   ORDER BY svseqno, Poseqno'
 
 EXEC (@SqlStr)
 
 
 DROP TABLE #PO;
 DROP TABLE #PVO;
 DROP TABLE #SV
more ▼

asked Sep 06, 2012 at 05:42 PM in Default

avatar image

mohan_kk2002
10 3 4 4

I copied and pasted this directly into ssms 2005 and im getting syntax errors all over. What version of sql are you using?

Sep 20, 2012 at 02:33 PM jjaroska
(comments are locked)
10|1200 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1066

asked: Sep 06, 2012 at 05:42 PM

Seen: 549 times

Last Updated: Sep 20, 2012 at 02:33 PM

Copyright 2016 Redgate Software. Privacy Policy