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
 General SQL Server Forums
 New to SQL Server Programming
 How to make row as column from view?

Author  Topic 

Vaishu
Posting Yak Master

178 Posts
Posted - 2009-08-11 : 18:16:43
HI

vwProducts

Prid---sku---------Desc------------Image
7------FGT---------Some desc-------img7
7------FGT---------Extra desc------img7
7------FGT---------usefull desc----img7
8------RTY---------Some desc-------imgRT
8------RTY---------usefull desc------imgRT
8------RTY---------Some desc-------imgRT1
8------RTY---------usefull desc------imgRT1

How Do I make ROW as COLUMN from the VIEW "vwProducts" then the expected result is like below.(I think need to make 2 Pivot column)

Prid--Sku--Desc1--Desc2-----Desc3------Image1---Image2----Image3
_________________________________________________________________
7--FGT--Some desc--Extra desc--usefull desc--img7-----Null-------Null
8--RTY--Some desc- Null-----=-usefull desc--imgRT----imgRT1-----Null

Please help
Avdance Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-08-11 : 18:52:17
1. Which version of SQL Server are you using?
2. Can there be more than 3 descriptions or 3 images for the result?


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

Vaishu
Posting Yak Master

178 Posts
Posted - 2009-08-11 : 18:58:48
Hi Peso

Version is SQL 2005 Standard
Maximum of 4 descriptions and 4 Images

quote:
Originally posted by Peso

1. Which version of SQL Server are you using?
2. Can there be more than 3 descriptions or 3 images for the result?


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-08-11 : 19:05:30
[code]SELECT prID,
sku,
MAX(CASE WHEN colID = 1 THEN [desc] ELSE NULL END) AS desc1,
MAX(CASE WHEN colID = 2 THEN [desc] ELSE NULL END) AS desc2,
MAX(CASE WHEN colID = 3 THEN [desc] ELSE NULL END) AS desc3,
MAX(CASE WHEN colID = 4 THEN [desc] ELSE NULL END) AS desc4,
MAX(CASE WHEN colID = 1 THEN [image] ELSE NULL END) AS image1,
MAX(CASE WHEN colID = 2 THEN [image] ELSE NULL END) AS image2,
MAX(CASE WHEN colID = 3 THEN [image] ELSE NULL END) AS image3,
MAX(CASE WHEN colID = 4 THEN [image] ELSE NULL END) AS image4
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY prID ORDER BY prID) AS colID,
prID,
sku,
[desc],
[image]
FROM vwProducts
) AS d
WHERE colID <= 4
GROUP BY prID,
sku[/code]

N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

Vaishu
Posting Yak Master

178 Posts
Posted - 2009-08-11 : 20:03:17
HI
It is working but not completly right

I am getting the below result

Prid--Sku--Desc1--Desc2-----Desc3------Image1---Image2----Image3
_________________________________________________________________
7--FGT--Some desc--Extra desc--usefull desc--img7-----img7-------img7
8--RTY--Some desc- Null-----=-usefull desc--imgRT----imgRT1-----Null

Instead of

Prid--Sku--Desc1--Desc2-----Desc3------Image1---Image2----Image3
_________________________________________________________________
7--FGT--Some desc--Extra desc--usefull desc--img7-----Null-------Null
8--RTY--Some desc- Null-----=-usefull desc--imgRT----imgRT1-----Null

Note :
One product may have more than one desc (maximum 4) and more than one image (maximum 4)
OR
One product may have one desc (maximum 4) and more than one image (maximum 4)
OR

One product may have more than one desc (maximum 4) and one image (maximum 4)

When I run the code its filling up the Image column for the equal number of Desc in the table(view). (i.e) If 2 Desc for a product it is giving value for 2 Image column when there is only ONE image.

Will you able to do somthing about it?. Images names are unique. Sorry for hassle

quote:
Originally posted by Peso

SELECT		prID,
		sku,
		MAX(CASE WHEN colID = 1 THEN [desc] ELSE NULL END) AS desc1,
		MAX(CASE WHEN colID = 2 THEN [desc] ELSE NULL END) AS desc2,
		MAX(CASE WHEN colID = 3 THEN [desc] ELSE NULL END) AS desc3,
		MAX(CASE WHEN colID = 4 THEN [desc] ELSE NULL END) AS desc4,
		MAX(CASE WHEN colID = 1 THEN [image] ELSE NULL END) AS image1,
		MAX(CASE WHEN colID = 2 THEN [image] ELSE NULL END) AS image2,
		MAX(CASE WHEN colID = 3 THEN [image] ELSE NULL END) AS image3,
		MAX(CASE WHEN colID = 4 THEN [image] ELSE NULL END) AS image4
FROM		(
			SELECT	ROW_NUMBER() OVER (PARTITION BY prID ORDER BY prID) AS colID,
				prID,
				sku,
				[desc],
				[image]
			FROM	vwProducts
		) AS d
WHERE		colID <= 4
GROUP BY	prID,
		sku


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-08-12 : 02:43:56
Do you have a identity column on the table? Or another primary key?

N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

Vaishu
Posting Yak Master

178 Posts
Posted - 2009-08-12 : 04:04:31
Hi

This view fetched from 5 tables and only the "Prid" is the Primary key. Only Description and Image tables have more than one rows for a "Prid" in the product table.All other tables have single rows.


quote:
Originally posted by Peso

Do you have a identity column on the table? Or another primary key?

N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

Vaishu
Posting Yak Master

178 Posts
Posted - 2009-08-12 : 17:00:27


Can anybody figure this out?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-08-12 : 17:27:37
Until you provide an answer how to know the order order records within each prID, no.


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

Vaishu
Posting Yak Master

178 Posts
Posted - 2009-08-14 : 06:07:02
HI

These are the below tables I am using to create a view and From the view trying to do "row as column.....".

Tblprod (Prid is primary key)

Prid---Sku---Price
7-------FGT----150.00
8-------RTY----240.00



TblDesc (DesID is primary key)

DesID---PrId---Title----Desc
1---------7------Main-----Some desc
2---------7------Extra----Extra desc
3---------7------Useful---usefull desc
4---------8------Main-----Some desc
5---------8------Useful----usefull desc



TblImg (Imgid is primary key)

Imgid----PrId---Image
1---------7------img7
2---------8------imgRT
3---------8------imgRT1



From the above 3 tables I have created a view and the results is like below

vwProducts

Prid---sku---------Desc------------Image
7------FGT---------Some desc-------img7
7------FGT---------Extra desc------img7
7------FGT---------usefull desc----img7
8------RTY---------Some desc-------imgRT
8------RTY---------usefull desc------imgRT
8------RTY---------Some desc-------imgRT1
8------RTY---------usefull desc------imgRT1

quote:
Originally posted by Peso

Until you provide an answer how to know the order order records within each prID, no.


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-08-14 : 06:35:08
Include DesID and ImgID columns in the view too.

N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

Vaishu
Posting Yak Master

178 Posts
Posted - 2009-08-14 : 07:41:08
Hi peso

I have included but still getting the same result.

quote:
Originally posted by Peso

Include DesID and ImgID columns in the view too.

N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-08-14 : 07:42:31
Now how does a sample from vwProducts look like?

imgid---desid---Prid------sku---------Desc------------Image
----?-------?------7------FGT---------Some desc-------img7
----?-------?------7------FGT---------Extra desc------img7
----?-------?------7------FGT---------usefull desc----img7
----?-------?------8------RTY---------Some desc-------imgRT
----?-------?------8------RTY---------usefull desc----imgRT
----?-------?------8------RTY---------Some desc-------imgRT1
----?-------?------8------RTY---------usefull desc----imgRT1


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

Vaishu
Posting Yak Master

178 Posts
Posted - 2009-08-14 : 10:23:06
Hi Peso

vWproducts look like you mentioned in your posting

quote:
Originally posted by Peso

Now how does a sample from vwProducts look like?

imgid---desid---Prid------sku---------Desc------------Image
----?-------?------7------FGT---------Some desc-------img7
----?-------?------7------FGT---------Extra desc------img7
----?-------?------7------FGT---------usefull desc----img7
----?-------?------8------RTY---------Some desc-------imgRT
----?-------?------8------RTY---------usefull desc----imgRT
----?-------?------8------RTY---------Some desc-------imgRT1
----?-------?------8------RTY---------usefull desc----imgRT1


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-08-14 : 10:25:24
Can you replace the question marks with the actual values?


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

Vaishu
Posting Yak Master

178 Posts
Posted - 2009-08-14 : 10:51:02
Hi

The view looks like below

imgid---desid---Prid------sku---------Desc------------Image
----4-------5------7------FGT---------Some desc-------img7
----4-------216----7------FGT---------Extra desc------img7
----4-------219----7------FGT---------usefull desc----img7
----5-------6------8------RTY---------Some desc-------imgRT
----5-------309----8------RTY---------usefull desc----imgRT
----102-----6------8------RTY---------Some desc-------imgRT1
----102-----309----8------RTY---------usefull desc----imgRT1



quote:
Originally posted by Peso

Now how does a sample from vwProducts look like?

imgid---desid---Prid------sku---------Desc------------Image
----?-------?------7------FGT---------Some desc-------img7
----?-------?------7------FGT---------Extra desc------img7
----?-------?------7------FGT---------usefull desc----img7
----?-------?------8------RTY---------Some desc-------imgRT
----?-------?------8------RTY---------usefull desc----imgRT
----?-------?------8------RTY---------Some desc-------imgRT1
----?-------?------8------RTY---------usefull desc----imgRT1


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

shalagur
Starting Member

0 Posts
Posted - 2009-08-15 : 04:09:33
do you mean this?

SELECT		prID,
		sku,
		MAX(CASE WHEN colID = 1 AND descseq=1 THEN [desc] ELSE NULL END) AS desc1,
		MAX(CASE WHEN colID = 2 AND descseq=2 THEN [desc] ELSE NULL END) AS desc2,
		MAX(CASE WHEN colID = 3 AND descseq=3 THEN [desc] ELSE NULL END) AS desc3,
		MAX(CASE WHEN colID = 4 AND descseq=4 THEN [desc] ELSE NULL END) AS desc4,
		MAX(CASE WHEN colID = 1 AND imageseq=1 THEN [image] ELSE NULL END) AS image1,
		MAX(CASE WHEN colID = 2 AND imageseq=2 THEN [image] ELSE NULL END) AS image2,
		MAX(CASE WHEN colID = 3 AND imageseq=3 THEN [image] ELSE NULL END) AS image3,
		MAX(CASE WHEN colID = 4 AND imageseq=4 THEN [image] ELSE NULL END) AS image4
FROM		(
			SELECT	ROW_NUMBER() OVER (PARTITION BY prID ORDER BY prID) AS colID,
DENSE_RANK() OVER (ORDER BY [desc]) AS [descseq],
DENSE_RANK() OVER (ORDER BY [image]) AS [imageseq],
				prID,
				sku,
				[desc],
				[image]
			FROM	vwProducts
		) AS d
WHERE		colID <= 4
GROUP BY	prID,
		sku


[/code]
Go to Top of Page

Vaishu
Posting Yak Master

178 Posts
Posted - 2009-08-15 : 14:25:02
Hi

Thanks for the post.This didn't give the expected result. Nearly 95 % of the column' is null except prId and sku.Probably we are close to the result. Could you please try a bit more to get correct result. Thank you
Go to Top of Page

Vaishu
Posting Yak Master

178 Posts
Posted - 2009-08-17 : 04:40:23
Hi

Any one got answer for this. Or How can I get the expected result from 3 table as in my posting?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-08-17 : 05:03:54
quote:
Originally posted by Vaishu

Could you please try a bit more to get correct result.
You are so funny! 
DECLARE	@Sample TABLE
	(
		imgID INT,
		desID INT,
		prID INT,
		sku CHAR(3),
		[desc] VARCHAR(20),
		[image] VARCHAR(20)
	)

INSERT	@Sample
SELECT	  4,   5, 7, 'FGT', 'Some desc',    'img7'   UNION ALL
SELECT	  4, 216, 7, 'FGT', 'Extra desc',   'img7'   UNION ALL
SELECT	  4, 219, 7, 'FGT', 'usefull desc', 'img7'   UNION ALL
SELECT	  5,   6, 8, 'RTY', 'Some desc',    'imgRT'  UNION ALL
SELECT	  5, 309, 8, 'RTY', 'usefull desc', 'imgRT'  UNION ALL
SELECT	102,   6, 8, 'RTY', 'Some desc',    'imgRT1' UNION ALL
SELECT	102, 309, 8, 'RTY', 'usefull desc', 'imgRT1'

SELECT		prID,
		sku,
		MAX(CASE WHEN colID = 1 THEN [desc] ELSE NULL END) AS desc1,
		MAX(CASE WHEN colID = 2 THEN [desc] ELSE NULL END) AS desc2,
		MAX(CASE WHEN colID = 3 THEN [desc] ELSE NULL END) AS desc3,
		MAX(CASE WHEN colID = 4 THEN [desc] ELSE NULL END) AS desc4,
		MAX(CASE WHEN colID = 1 THEN [image] ELSE NULL END) AS image1,
		MAX(CASE WHEN colID = 2 THEN [image] ELSE NULL END) AS image2,
		MAX(CASE WHEN colID = 3 THEN [image] ELSE NULL END) AS image3,
		MAX(CASE WHEN colID = 4 THEN [image] ELSE NULL END) AS image4
FROM		(
			SELECT	DENSE_RANK() OVER (PARTITION BY prID ORDER BY imgID) AS colID,
				prID,
				sku,
				[desc],
				[image]
			FROM	@Sample
		) AS d
WHERE		colID <= 4
GROUP BY	prID,
		sku


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

Vaishu
Posting Yak Master

178 Posts
Posted - 2009-08-17 : 06:53:25
Hi Peso

From your code. Now the Images are fine but description not working right. If you run this code you can see "desc" for product ID "8" repeating instead of picking the right one.

quote:
Originally posted by Peso

quote:
Originally posted by Vaishu

Could you please try a bit more to get correct result.
You are so funny! 
DECLARE	@Sample TABLE
	(
		imgID INT,
		desID INT,
		prID INT,
		sku CHAR(3),
		[desc] VARCHAR(20),
		[image] VARCHAR(20)
	)

INSERT	@Sample
SELECT	  4,   5, 7, 'FGT', 'Some desc',    'img7'   UNION ALL
SELECT	  4, 216, 7, 'FGT', 'Extra desc',   'img7'   UNION ALL
SELECT	  4, 219, 7, 'FGT', 'usefull desc', 'img7'   UNION ALL
SELECT	  5,   6, 8, 'RTY', 'Some desc',    'imgRT'  UNION ALL
SELECT	  5, 309, 8, 'RTY', 'usefull desc', 'imgRT'  UNION ALL
SELECT	102,   6, 8, 'RTY', 'Some desc',    'imgRT1' UNION ALL
SELECT	102, 309, 8, 'RTY', 'usefull desc', 'imgRT1'

SELECT		prID,
		sku,
		MAX(CASE WHEN colID = 1 THEN [desc] ELSE NULL END) AS desc1,
		MAX(CASE WHEN colID = 2 THEN [desc] ELSE NULL END) AS desc2,
		MAX(CASE WHEN colID = 3 THEN [desc] ELSE NULL END) AS desc3,
		MAX(CASE WHEN colID = 4 THEN [desc] ELSE NULL END) AS desc4,
		MAX(CASE WHEN colID = 1 THEN [image] ELSE NULL END) AS image1,
		MAX(CASE WHEN colID = 2 THEN [image] ELSE NULL END) AS image2,
		MAX(CASE WHEN colID = 3 THEN [image] ELSE NULL END) AS image3,
		MAX(CASE WHEN colID = 4 THEN [image] ELSE NULL END) AS image4
FROM		(
			SELECT	DENSE_RANK() OVER (PARTITION BY prID ORDER BY imgID) AS colID,
				prID,
				sku,
				[desc],
				[image]
			FROM	@Sample
		) AS d
WHERE		colID <= 4
GROUP BY	prID,
		sku


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site