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

mohan_kk2002 gravatar image

mohan_kk2002
10 1 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x986

asked: Sep 06, 2012 at 05:42 PM

Seen: 471 times

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