How to get n number of rows in a Multiple columns

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-07-02 : 06:56:41

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

52326 Posts

Posted - 2013-07-02 : 07:07:39

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

shinelawrence
Starting Member

32 Posts

Posted - 2013-07-02 : 07:10:43

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

Lawce

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-02 : 07:14:59

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

shinelawrence
Starting Member

32 Posts

Posted - 2013-07-02 : 07:21:26

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

Lawce

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-02 : 07:22:19

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

shinelawrence
Starting Member

32 Posts

Posted - 2013-07-02 : 07:24:06

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-02 : 07:31:03

[code]
;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
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

shinelawrence
Starting Member

32 Posts

Posted - 2013-07-02 : 07:40:22

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

shinelawrence
Starting Member

32 Posts

Posted - 2013-07-02 : 07:42:29

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-02 : 07:46:58

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

shinelawrence
Starting Member

32 Posts

Posted - 2013-07-02 : 07:51:30

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

Lawce

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-02 : 07:55:48

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

shinelawrence
Starting Member

32 Posts

Posted - 2013-07-02 : 23:53:26

ok...sure....thanks

Lawce

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources