Click here to Skip to main content
15,078,444 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
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
Updated 11-Nov-13 0:51am
v5

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
   
v2
Comments
Vishal Pand3y 12-Nov-13 0:21am
   
@Thava Rajan Although it is executed without any error but output is still same ie Like
UserID Name StateName SubProductCatName StartDate EndDate
1 Vishal Delhi (NCT) Other Adhesives 2013-08-01 00:00:00.000 2013-08-31 00:00:00.000
1 Vishal Delhi (NCT) Sealants 2013-08-01 00:00:00.000 2013-08-31 00:00:00.000
use Concate or Concatinate function in SQL
   
Comments
Vishal Pand3y 11-Nov-13 7:13am
   
@harsh can u explain bcs i haven't use any function like that....
http://msdn.microsoft.com/en-us/library/ms190349.aspx[^] try this or if it doesn't work i will have to write it myself
   
The solution I am going to give you not in your format. You have you to create for your own.
Please Visit Me
   
v5

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900