将多行组合成单行 [英] combine Multiple rows into single row

问题描述

在我的数据库中我有一个存储过程，其中我得到所有付费用户的列表

像这样

 UserID名称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 

但我想要这些信息喜欢这个

 UserID名称StateName SubProductCatName StartDate EndDate 
 1 Vishal Delhi（NCT），Goa其他粘合剂，密封剂2013-08-01 2013-08-31 

最初我的存储过程是

 更改  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  ValueTyp e VT1  ON  PT.SubProductCategoryID = VT1.ValueTypeID 
  INNER   JOIN  UserDetail UD  ON  UPD.UserID = UD.UserID 
  ORDER   BY  UPD.UserID 
  END  

现在我已将其更改为

  ALTER  程序 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 
 其中 UPD.Enabled = 1 
  ORDER   BY  UPD.UserID 
  END  

但它仍然以相同的方式显示数据虽然我已经使用过xml

任何人都可以帮助......

解决方案

似乎你需要像这样改变它

  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 
 其中 UPD.Enabled = 1 
  ORDER   BY  UPD.UserID 
  END  

希望完全现在你理解我认为

在SQL中使用Concate或Concatinate函数

http://stackoverflow.com / questions / 8005846 / sql-server-combining-multiple-rows-into-one-row [ ^ ]见上面的链接

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

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

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

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

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......

解决方案

seems you need to change it like this

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

use Concate or Concatinate function in SQL

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

这篇关于将多行组合成单行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！