combine Multiple rows into single row

Question

5.00/5 (1 vote)

See more:

In my Database I have a Stored procedure in which i am getting list of all Paid user
Like this

HTML

UserID	Name	StateName	StateID	SubProductCatName	SubProductCatID	StartDate	EndDate
1	Vishal	Delhi (NCT)	80164	Other Adhesives	87045	2013-08-01 00:00:00.000	2013-08-31 00:00:00.000
1	Vishal	Goa	80164	Sealants	87046	2013-08-01 00:00:00.000	2013-08-31 00:00:00.000

but i want these information Like This

HTML

UserID	Name	StateName	SubProductCatName	StartDate	EndDate
1	Vishal	Delhi (NCT),Goa	   Other Adhesives,Sealants 2013-08-01  2013-08-31

Initially my stored Procedure was

SQL

Alter PROCEDURE [dbo].[spGetAllPaidUserList]  
AS  
    BEGIN              
        SELECT  UPD.UserID ,  
                UD.Name ,  
                VT.ValueTypeName AS StateName ,  
                PT.StateID ,  
                VT1.ValueTypeName AS SubProductCatName ,  
                PT.SubProductCategoryID AS SubProductCatID ,  
                StartDate ,  
                EndDate  
        FROM    UserPaymentDetail UPD  
                INNER JOIN PaymentType PT ON UPD.PaymentTypeID = PT.PaymentTypeID  
                INNER JOIN ValueType VT ON PT.StateID = VT.ValueTypeID  
                INNER JOIN ValueType VT1 ON PT.SubProductCategoryID = VT1.ValueTypeID  
                INNER JOIN UserDetail UD ON UPD.UserID = UD.UserID  
        ORDER BY UPD.UserID    
    END

Now i have changed it to

SQL

ALTER PROCEDURE spGetAllPaidUserList    
AS    
    BEGIN                
        SELECT  UPD.UserID ,    
                UD.Name , 
                Stuff((SELECT '; ' + VT.ValueTypeName FROM ValueType VT
                WHERE  VT.ValueTypeID=PT.StateID
                 FOR XML PATH('')), 1, 1, '')                    
                [VT.ValueTypeName],-- AS StateName ,    
                Stuff((SELECT '; ' + VT1.ValueTypeName FROM ValueType VT1
                WHERE  VT1.ValueTypeID=PT.SubProductCategoryID
                 FOR XML PATH('')), 1, 1, '') 
                [VT1.ValueTypeName], --AS SubProductCatName ,
                StartDate ,    
                EndDate    
        FROM    UserPaymentDetail UPD    
                INNER JOIN PaymentType PT ON UPD.PaymentTypeID = PT.PaymentTypeID    
                INNER JOIN ValueType VT ON PT.StateID = VT.ValueTypeID    
                INNER JOIN ValueType VT1 ON PT.SubProductCategoryID = VT1.ValueTypeID    
                INNER JOIN UserDetail UD ON UPD.UserID = UD.UserID    
        where UPD.Enabled=1  
        ORDER BY UPD.UserID 
    END

but it Still show the data in same way although I have used xml
can anyone help......

Posted 10-Nov-13 23:42pm

Vishal Pand3y

Updated 11-Nov-13 0:51am

v5

Add a Solution

5 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Thava Rajan · Answer 1 · 2013-11-11T03:11:00

seems you need to change it like this

SQL

ALTER PROCEDURE spGetAllPaidUserList
AS
    BEGIN
        SELECT  UPD.UserID ,
                UD.Name ,
                Stuff((SELECT '; ' + VT.ValueTypeName FROM ValueType VT
                WHERE  VT.ValueTypeID=PT.StateID
                 FOR XML PATH('')), 1, 1, '') AS StateName ,
                Stuff((SELECT '; ' + VT1.ValueTypeName FROM ValueType VT1
                WHERE  VT1.ValueTypeID=PT.SubProductCategoryID
                 FOR XML PATH('')), 1, 1, '') AS SubProductCatName ,
                StartDate ,
                EndDate
        FROM    UserPaymentDetail UPD
                INNER JOIN PaymentType PT ON UPD.PaymentTypeID = PT.PaymentTypeID
                INNER JOIN UserDetail UD ON UPD.UserID = UD.UserID
        where UPD.Enabled=1
        ORDER BY UPD.UserID
    END

hope fully now you understand i think

agent_kruger · Answer 2 · 2013-11-11T01:05:00

Solution 1

use Concate or Concatinate function in SQL

Posted 11-Nov-13 1:05am

agent_kruger

Comments

Vishal Pand3y 11-Nov-13 7:13am

@harsh can u explain bcs i haven't use any function like that....

agent_kruger · Answer 3 · 2013-11-11T01:32:00

Solution 3

http://stackoverflow.com/questions/8005846/sql-server-combining-multiple-rows-into-one-row[^] see the above link

Posted 11-Nov-13 1:32am

agent_kruger

agent_kruger · Answer 4 · 2013-11-11T01:45:00

Solution 4

http://msdn.microsoft.com/en-us/library/ms190349.aspx[^] try this or if it doesn't work i will have to write it myself

Posted 11-Nov-13 1:45am

agent_kruger

Amitava Bag (Kolkata) · Answer 5 · 2013-11-11T02:15:00

Solution 5

The solution I am going to give you not in your format. You have you to create for your own.
Please Visit Me

Posted 11-Nov-13 2:15am

Amitava Bag (Kolkata)

Updated 11-Nov-13 3:02am

v5

combine Multiple rows into single row

5 solutions

Solution 7

Solution 1

Solution 3

Solution 4

Solution 5

Add your solution here

Preview 0

combine Multiple rows into single row

5 solutions

Solution 7

Solution 1

Solution 3

Solution 4

Solution 5

Add your solution here

Preview 0

Existing Members

...or Join us