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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 How to get n number of rows in a Multiple columns

Author  Topic 

shinelawrence
Starting Member

32 Posts

Posted - 2013-01-29 : 23:44:00
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

52326 Posts

Posted - 2013-01-29 : 23:47:40
[code]
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
[/code]

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

Go to Top of Page

shinelawrence
Starting Member

32 Posts

Posted - 2013-01-30 : 00:50:28
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
Go to Top of Page

sqlbay
Starting Member

12 Posts

Posted - 2013-01-30 : 02:47:48
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

52326 Posts

Posted - 2013-01-30 : 03:11:44
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

32 Posts

Posted - 2013-01-30 : 04:02:12
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

52326 Posts

Posted - 2013-01-30 : 04:15:35
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

32 Posts

Posted - 2013-01-31 : 06:06:37
I'm sorry...Thank u for ur kindly help....


Lawce
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-31 : 08:54:37
welcome

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

Go to Top of Page
   

- Advertisement -