How to fetch coloum as row from nultiple table? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 fetch coloum as row from nultiple table?

Author

Topic

Vaishu
Posting Yak Master

178 Posts

Posted - 2009-08-11 : 05:50:01

TblDesc

PrId---Title----Desc
7------Main-----Some description
7------Extra----Some description
7------Useful---Some description
8------Main-----Some description
8------Extra----Some description
9------Main-----Some description

TblImg

PrId---Image
7------img7
8------imgRT
8------imgRT1
9------imgTS
9------imgTS1
9------imgTS2

How do write view or Procedure to query the rows as coloums as below

PrID---Col1----Col2----Col3------img1----img2----img3
7------Main----Extra---Useful-----img7---Null----Null
8------Main----Extra---Null ------imgRT--imgRT1--Null
9------Main----Null----Null-------ImgTs--imgTS1--imgTS2

I am using the below query to fetch records from ONE TABLE (posted by Peso in this Forum and I have altered little bit)

SELECT	p.PrID,
		p.[1] AS Col1,
		p.[2] AS Col2,
		p.[3] AS Col3,
		p.[4] AS Col3,
p.[5] AS Col3
FROM		(
			SELECT	Prid,
				Title,
				ROW_NUMBER() OVER (PARTITION BY Prid ORDER BY Title) AS colID
			FROM dashCommerce_Store_Product_Category_Map
		) AS d
PIVOT		
      (
			MAX(Title)
			FOR colID IN ([1], [2], [3],[4],[5])
		) AS p
ORDER BY	p.PrId

Also after this result I have 4 more table to pull the records using "JOIN". Advance Thank you

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-11 : 07:53:37

see this link
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

Vaishu
Posting Yak Master

178 Posts

Posted - 2009-08-11 : 11:11:47

Hi

Any other idea. The link doesn't help. Any sample code ???

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-11 : 21:16:01

quote:
Also after this result I have 4 more table to pull the records using "JOIN".

You need to gives us more information . .

Without much information, i assumed it is just a simple JOIN to other table ? ?


select *
from
(
SELECT	p.PrID,
		p.[1] AS Col1,
		p.[2] AS Col2,
		p.[3] AS Col3,
		p.[4] AS Col3,
p.[5] AS Col3
FROM		(
			SELECT	Prid,
				Title,
				ROW_NUMBER() OVER (PARTITION BY Prid ORDER BY Title) AS colID
			FROM dashCommerce_Store_Product_Category_Map
		) AS d
PIVOT		
      (
			MAX(Title)
			FOR colID IN ([1], [2], [3],[4],[5])
		) AS p
) a
inner join table1 t1 on . . .
inner join table2 t2 on . . .
. . .

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

Vaishu
Posting Yak Master

178 Posts

Posted - 2009-08-12 : 12:37:38

Hi

Could you please look at the below posting?. Realy need help

[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=131029[/url]

quote:
Originally posted by khtan

quote:
Also after this result I have 4 more table to pull the records using "JOIN".

You need to gives us more information . .

Without much information, i assumed it is just a simple JOIN to other table ? ?

select *
from
(
SELECT p.PrID,
p.[1] AS Col1,
p.[2] AS Col2,
p.[3] AS Col3,
p.[4] AS Col3,
p.[5] AS Col3
FROM (
SELECT Prid,
Title,
ROW_NUMBER() OVER (PARTITION BY Prid ORDER BY Title) AS colID
FROM dashCommerce_Store_Product_Category_Map
) AS d
PIVOT
(
MAX(Title)
FOR colID IN ([1], [2], [3],[4],[5])
) AS p
) a
inner join table1 t1 on . . .
inner join table2 t2 on . . .
. . .

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources