SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Help in pivot sort of query		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kamii47
Constraint Violating Yak Guru

335 Posts
Posted - 03/07/2013 :  07:26:34  Show Profile  Reply with Quote
I have a query

Select bc.Category
, bc.CatID
, bec.EntryCatID
From Blog_Categories bc
Inner Join Blog_Entry_Categories bec
On bc.CatID = bec.CatID
Where bc.CatID In( 6, 14 )

which is giving result set

Caegory catid entrycatid

Family 6 15793
Family 6 15796
Family 6 15811
Family 6 15831
Accident 14 15813
Accident 14 15965
Accident 14 16130
Accident 14 16376
Accident 14 16378
Accident 14 16380

I wants the result set as

Caegory catid col1 col2 col3 col4 col5 col6
Family 6 15793 15796 15811 15831 NULL NULL
Accident 14 15813 15965 16130 16376 16378 16380

Kamran Shahid
Principle Engineer Development
(MCSD.Net,MCPD.net)

visakh16
Very Important crosS Applying yaK Herder

India
47099 Posts
Posted - 03/07/2013 :  09:13:52  Show Profile  Reply with Quote 

SELECT *
FROM
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY Category,catid ORDER BY entrycatid ASC) AS Seq
FROM Table
)t
PIVOT (MAX(entrycatid) FOR Seq IN ([1],[2],[3],[4],[5],[6]))p


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
1430 Posts
Posted - 03/08/2013 :  02:21:24  Show Profile  Reply with Quote
If number of EntryCatIDs per CatId is not fixed then follow this link:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=180401

--
Chandu
Edited by - bandi on 03/08/2013 02:21:56
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000