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 2008 Forums
 Transact-SQL (2008)
 How to get n number of rows in a Multiple columns
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

shinelawrence
Starting Member

India
32 Posts

Posted - 01/29/2013 :  23:44:00  Show Profile  Reply with Quote
Hi Everyone, I have 5 rows in my table I want to get Multiple column for each row. How to do... My Current O/P:
AppNo AppName
100 Lawrence
100 Smith
100 Raja
100 Harsha
100 Fedal
I need like this
O/P:

AppNo AppName1 AppName2 AppName3 AppName4 AppName5
100 Lawrence Smith Raja Harsha Fedal

How to get... Just tell he solution...
Thanks In Advanced

Lawce

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/29/2013 :  23:47:40  Show Profile  Reply with Quote

SELECT AppNo,
[1] AS AppName1,
[2] AS AppName2,
[3] AS AppName3,
[4] AS AppName4,
[5] AS AppName5
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY AppNo ORDER BY AppNo) AS Seq,*
FROM Table
)t
PIVOT(MAX(AppName) FOR Seq IN ([1],[2],[3],[4],[5]))p


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

shinelawrence
Starting Member

India
32 Posts

Posted - 01/30/2013 :  00:50:28  Show Profile  Reply with Quote
thanks for ur reply... It's working but i need only one row... Here i got sepearate columns but same 5 rows... i need one row only...Just tell the solution...


Thanks In Advanced

Lawce

Edited by - shinelawrence on 01/30/2013 02:30:46
Go to Top of Page

sqlbay
Starting Member

12 Posts

Posted - 01/30/2013 :  02:47:48  Show Profile  Reply with Quote
It is giving only single row..

AppNo       AppName1   AppName2   AppName3  AppName4  AppName5

-------     --------   ---------  --------- --------- --------

100         Lawrence   Smith      Raja      Harsha    Fedal


SQL Server Professional http://sqlbay.blogspot.in
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/30/2013 :  03:11:44  Show Profile  Reply with Quote
quote:
Originally posted by shinelawrence

thanks for ur reply... It's working but i need only one row... Here i got sepearate columns but same 5 rows... i need one row only...Just tell the solution...


Thanks In Advanced

Lawce


do it properly and you'll get only one row.
if your table is different from what posted above then post how exactly is your data.
We cant see your data and cant guess whats happening at your end unless you give us full info

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

shinelawrence
Starting Member

India
32 Posts

Posted - 01/30/2013 :  04:02:12  Show Profile  Reply with Quote
I used this query and my original table is
SELECT * FROM LCA_Doclist_detail

PK_ID ApplicationNo_FK ApplicantName
1 A100 Lawrence
2 A100 Naganathan
3 A100 Fedal
4 A100 Sathya
5 A100 Natarajan

The above query i used, This is the query and output...
SELECT ApplicationNo_FK,
[1] AS AppName1,
[2] AS AppName2,
[3] AS AppName3,
[4] AS AppName4,
[5] AS AppName5
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY ApplicationNo_FK ORDER BY ApplicationNo_FK) AS Seq,*
FROM LCA_Doclist_detail WHERE ApplicationNo_FK='A100'
)t
PIVOT(MAX(ApplicantName) FOR Seq IN ([1],[2],[3],[4],[5]))p

ApplicationNo_FK AppName1 AppName2 AppName3 AppName4 AppName5
A100 Lawrence NULL NULL NULL NULL
A100 NULL Naganathan NULL NULL NULL
A100 NULL NULL Fedal NULL NULL
A100 NULL NULL NULL Sathya NULL
A100 NULL NULL NULL NULL Natarajan


but i need like this output...

ApplicationNo_FK AppName1 AppName2 AppName3 AppName4 AppName5
A100 Lawrence Naganathan Fedal Sathya Natarajan

I need the output like this...How to do..Just tell the solution...

Thanks In Advanced

Lawce
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/30/2013 :  04:15:35  Show Profile  Reply with Quote
How do you think we can guess presence of an extra column unless you tell us?

SELECT ApplicationNo_FK,
[1] AS AppName1,
[2] AS AppName2,
[3] AS AppName3,
[4] AS AppName4,
[5] AS AppName5
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY ApplicationNo_FK ORDER BY ApplicationNo_FK) AS Seq,	ApplicationNo_FK,	 ApplicantName
FROM LCA_Doclist_detail
)t
PIVOT(MAX(ApplicantName) FOR Seq IN ([1],[2],[3],[4],[5]))p


Also
I need the output like this...How to do..Just tell the solution...
Please be a little more courteous next time asking for help.
Also try to learn the solutions provided rather than just trying to get it from someone otherwise you wont benefit anything from this

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

shinelawrence
Starting Member

India
32 Posts

Posted - 01/31/2013 :  06:06:37  Show Profile  Reply with Quote
I'm sorry...Thank u for ur kindly help....


Lawce
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/31/2013 :  08:54:37  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

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.06 seconds. Powered By: Snitz Forums 2000