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 - 07/02/2013 :  06:56:41  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 Type
100 Lawrence Individual
100 Smith Partner
100 Raja Private
100 Harsha Public
100 Fedal Individual
I need like this
O/P:

AppNo AppName1 AppName2 AppName3 AppName4 AppName5 Type1 Type2
100 Lawrence Smith Raja Harsha Fedal Individual Partn

Type3 Type4 Type5
Private Public Individual

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

Lawce

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/02/2013 :  07:07:39  Show Profile  Reply with Quote
How to get... Just tell the solution...
What a way to ask for spoonfed answers! I'm not intending to give you spoonfed just for this one statement

Look for cross tabing in google and you'll get lots of solutions
ALso look for syntax of PIVOT in books online

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

shinelawrence
Starting Member

India
32 Posts

Posted - 07/02/2013 :  07:10:43  Show Profile  Reply with Quote
no...not like that... I don't know how to use two pivot in one query...Please tell the solution....

Lawce
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/02/2013 :  07:14:59  Show Profile  Reply with Quote
quote:
Originally posted by shinelawrence

no...not like that... I don't know how to use two pivot in one query...Please tell the solution....

Lawce


see this and try it yourself.
Post if you face any issues

https://www.simple-talk.com/sql/t-sql-programming/creating-cross-tab-queries-and-pivot-tables-in-sql/

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

shinelawrence
Starting Member

India
32 Posts

Posted - 07/02/2013 :  07:21:26  Show Profile  Reply with Quote
I'll get partial but the type column shown empty.... Please tell the solution....

Lawce
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/02/2013 :  07:22:19  Show Profile  Reply with Quote
quote:
Originally posted by shinelawrence

I'll get partial but the type column shown empty.... Please tell the solution....

Lawce


show your used code

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

shinelawrence
Starting Member

India
32 Posts

Posted - 07/02/2013 :  07:24:06  Show Profile  Reply with Quote
SELECT ApplicationNo,ApplicantName,ISNULL([1],'') AS AppName1,ISNULL([2],'') AS AppName2,ISNULL([3],'') AS AppName3,ISNULL([4],'') AS AppName4,Pk_id,ISNULL([6],'') AS Guar1,ISNULL([7],'') AS Guar2,ISNULL(,'') AS Guar3,ISNULL([9],'') AS Guar4,ISNULL([10],'') AS Guar5,GuarConstitution FROM(SELECT ROW_NUMBER() OVER (PARTITION BY A.ApplicationNo ORDER BY A.ApplicationNo) AS Seq,A.ApplicationNo,G.GuarantorName,A.ApplicantName,A.Unitshrtdesr,A.Pk_id,G.GuarConstitution FROM Loln_caApplicantInfo A LEFT JOIN Loln_caGuarantor G ON G.ApplicationNo=A.ApplicationNo WHERE A.Unitshrtdesr='ABR' AND A.ApplicationNo NOT IN (SELECT ApplicationNo FROM Loln_caDoclist) AND A.ApplicationNo='ABR0000002')t PIVOT(MAX(GuarantorName) FOR Seq IN ([1],[2],[3],[4],[5],[6],[7],,[9],[10])) AS p

Lawce
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/02/2013 :  07:31:03  Show Profile  Reply with Quote

;With CTE
AS
(SELECT ROW_NUMBER() OVER (PARTITION BY A.ApplicationNo ORDER BY A.ApplicationNo) AS Seq,A.ApplicationNo,G.GuarantorName,A.ApplicantName,A.Unitshrtdesr,A.Pk_id,G.GuarConstitution 
FROM Loln_caApplicantInfo A 
LEFT JOIN Loln_caGuarantor G 
ON G.ApplicationNo=A.ApplicationNo 
WHERE A.Unitshrtdesr='ABR' 
AND A.ApplicationNo NOT IN (SELECT ApplicationNo FROM Loln_caDoclist) 
AND A.ApplicationNo='ABR0000002'
)

SELECT m.ApplicationNo,
m.[1] AS AppName1,
m.[2] AS AppName2,
m.[3] AS AppName3,
m.[4] AS AppName4,
m.[5] AS AppName5,
n.[1] AS Guar1,
n.[2] AS Guar2,
n.[3] AS Guar3,
n.[4] AS Guar4,
n.[5] AS Guar5
FROM
(
SELECT  *
FROM (SELECT ApplicationNo,Seq,ApplicantName
CTE )c1
PIVOT(MAX(ApplicantName) FOR Seq IN ([1],[2],[3],[4],[5]))p
)m
INNER JOIN 
(
SELECT  *
FROM (SELECT ApplicationNo,Seq,GuarantorName
CTE )c1
PIVOT(MAX(GuarantorName) FOR Seq IN ([1],[2],[3],[4],[5]))p
)n
ON n.ApplicationNo = m.ApplicationNo
AND n.Seq = m.Seq



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

shinelawrence
Starting Member

India
32 Posts

Posted - 07/02/2013 :  07:40:22  Show Profile  Reply with Quote
i got these errors:

Msg 207, Level 16, State 1, Line 26
Invalid column name 'ApplicationNo'.
Msg 207, Level 16, State 1, Line 26
Invalid column name 'Seq'.
Msg 207, Level 16, State 1, Line 26
Invalid column name 'ApplicantName'.
Msg 207, Level 16, State 1, Line 28
Invalid column name 'ApplicantName'.
Msg 207, Level 16, State 1, Line 33
Invalid column name 'ApplicationNo'.
Msg 207, Level 16, State 1, Line 33
Invalid column name 'Seq'.
Msg 207, Level 16, State 1, Line 33
Invalid column name 'GuarantorName'.
Msg 207, Level 16, State 1, Line 35
Invalid column name 'GuarantorName'.
Msg 207, Level 16, State 1, Line 38
Invalid column name 'Seq'.
Msg 207, Level 16, State 1, Line 38
Invalid column name 'Seq'.

Lawce
Go to Top of Page

shinelawrence
Starting Member

India
32 Posts

Posted - 07/02/2013 :  07:42:29  Show Profile  Reply with Quote
Now got 2 errors:

Msg 207, Level 16, State 1, Line 36
Invalid column name 'Seq'.
Msg 207, Level 16, State 1, Line 36
Invalid column name 'Seq'.


Lawce
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/02/2013 :  07:46:58  Show Profile  Reply with Quote
quote:
Originally posted by shinelawrence

Now got 2 errors:

Msg 207, Level 16, State 1, Line 36
Invalid column name 'Seq'.
Msg 207, Level 16, State 1, Line 36
Invalid column name 'Seq'.


Lawce


Show your used query if different from mine

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

shinelawrence
Starting Member

India
32 Posts

Posted - 07/02/2013 :  07:51:30  Show Profile  Reply with Quote
thanks....i solved that issue.... thank u very much....

Lawce
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/02/2013 :  07:55:48  Show Profile  Reply with Quote
quote:
Originally posted by shinelawrence

thanks....i solved that issue.... thank u very much....

Lawce


Ok..Good that you got it sorted out

Now try to understand the query by reading PIVOT syntax and usage in MSDN or books online

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

shinelawrence
Starting Member

India
32 Posts

Posted - 07/02/2013 :  23:53:26  Show Profile  Reply with Quote
ok...sure....thanks

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