请参阅以下链接



http://***.com/questions/8005846/sql-server-combining-multiple-行 - 一行 [ ^ ]



http://***.com/questions/11213235/sql-select-multiple-rows-into-one-displayed-row [ ^ ]

Refer below links

http://***.com/questions/8005846/sql-server-combining-multiple-rows-into-one-row[^]

http://***.com/questions/11213235/sql-select-multiple-rows-into-one-displayed-row[^]

ALTER PROCEDURE [dbo].[Pr_TimeCardReport2]
	  @EM		  VARCHAR(MAX),
      @SD         DATETIME,
      @ED         DATETIME
   
AS
BEGIN

      DECLARE           @Sql VARCHAR(8000)
      DECLARE           @Index INT
      SET               @Index = 0

      SET @Sql =  'SELECT           TC.ID, TC.Start, TC.Finish, TC.EmpMail,
                        (SELECT OUTTime FROM TimeCardBreaks WHERE BreakType = ''Lunch''
                              AND TCID = TC.ID) [L_Out],
                        (SELECT INTime FROM TimeCardBreaks WHERE BreakType = ''Lunch''
                              AND TCID = TC.ID) [L_In], '

      WHILE (@Index < 10)
      BEGIN
            SET @Sql = @Sql + '(SELECT OUTTime FROM TimeCardBreaks TCB WHERE BreakType = ''Smoke''
                        AND TCID = TC.ID
                        AND ' + CAST(@Index AS VARCHAR) + ' = (SELECT COUNT(Id) FROM TimeCardBreaks WHERE BreakType = ''Smoke'' AND TCID = TC.ID
                        AND ID < TCB.ID)) [S' + CAST(@Index+1 AS VARCHAR) + '_Out],
                        (SELECT INTime FROM TimeCardBreaks TCB WHERE BreakType = ''Smoke''
                        AND TCID = TC.ID
                        AND ' + CAST(@Index AS VARCHAR) + ' = (SELECT COUNT(Id) FROM TimeCardBreaks WHERE BreakType = ''Smoke'' AND TCID = TC.ID
                        AND ID < TCB.ID)) [S' + CAST(@Index+1 AS VARCHAR) + '_In], '
            SET @Index = @Index + 1
      END                     

      SET @Sql = SUBSTRING(@Sql, 1, LEN(@Sql)-1)
      SET @Sql = @Sql + ' FROM      TimeCard TC WHERE EmpMail = ''' +@EM+ ''' AND Start >= ''' + CAST(@SD AS VARCHAR)+ ''' AND Finish <= ''' + CAST(@ED AS VARCHAR) + ''''

      EXEC (@Sql)
     
END

使用

Hi,

use

for xml path()

，



它为您提供了一个特定列的xml字符串，



例如，

,

it gives you a xml string of a perticular column,

for example,

USE AdventureWorks2008R2
SELECT      CAT.Name AS [Category],
            STUFF((    SELECT ',' + SUB.Name AS [text()]
                        – Add a comma (,) before each value
                        FROM Production.ProductSubcategory SUB
                        WHERE
                        SUB.ProductCategoryID = CAT.ProductCategoryID
                        FOR XML PATH('') – Select it as XML
                        ), 1, 1, '' )
                        – This is done to remove the first character (,)
                        – from the result
            AS [Sub Categories]
FROM  Production.ProductCategory CAT

谢谢。

Thanks.