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 AdvancedLawce |
|
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 AppName5FROM(SELECT ROW_NUMBER() OVER (PARTITION BY AppNo ORDER BY AppNo) AS Seq,*FROM Table)tPIVOT(MAX(AppName) FOR Seq IN ([1],[2],[3],[4],[5]))p[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
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 AdvancedLawce |
|
|
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 |
|
|
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 AdvancedLawce
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 MVPhttp://visakhm.blogspot.com/ |
|
|
shinelawrence
Starting Member
32 Posts |
Posted - 2013-01-30 : 04:02:12
|
I used this query and my original table isSELECT * FROM LCA_Doclist_detailPK_ID ApplicationNo_FK ApplicantName1 A100 Lawrence2 A100 Naganathan3 A100 Fedal4 A100 Sathya5 A100 NatarajanThe 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 AppName5FROM(SELECT ROW_NUMBER() OVER (PARTITION BY ApplicationNo_FK ORDER BY ApplicationNo_FK) AS Seq,*FROM LCA_Doclist_detail WHERE ApplicationNo_FK='A100')tPIVOT(MAX(ApplicantName) FOR Seq IN ([1],[2],[3],[4],[5]))pApplicationNo_FK AppName1 AppName2 AppName3 AppName4 AppName5A100 Lawrence NULL NULL NULL NULLA100 NULL Naganathan NULL NULL NULLA100 NULL NULL Fedal NULL NULLA100 NULL NULL NULL Sathya NULLA100 NULL NULL NULL NULL Natarajanbut i need like this output...ApplicationNo_FK AppName1 AppName2 AppName3 AppName4 AppName5A100 Lawrence Naganathan Fedal Sathya NatarajanI need the output like this...How to do..Just tell the solution...Thanks In AdvancedLawce |
|
|
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 AppName5FROM(SELECT ROW_NUMBER() OVER (PARTITION BY ApplicationNo_FK ORDER BY ApplicationNo_FK) AS Seq, ApplicationNo_FK, ApplicantNameFROM LCA_Doclist_detail)tPIVOT(MAX(ApplicantName) FOR Seq IN ([1],[2],[3],[4],[5]))p AlsoI 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 MVPhttp://visakhm.blogspot.com/ |
|
|
shinelawrence
Starting Member
32 Posts |
Posted - 2013-01-31 : 06:06:37
|
I'm sorry...Thank u for ur kindly help....Lawce |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-31 : 08:54:37
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|