SQL Server:行成列 [英] SQL Server : rows into columns

问题描述

我有三个桌子.我需要将行值转换为列.

I have three tables. I need to swith row values into columns.

表1:[Approval_Type]

App_ID  Type_Ds
2   RMC2
1   RMC1

表2:[Project]

Pro_id  Summary
1   PROJECT1
2   PROJECT2

表:3 [Prj_App]

App_Id  Pro_Id  ExpDt                           ComDt
1   2   2010-06-05                         2010-07-06 
1   1   1999-05-05                        1999-05-06
2   1   1900-01-01                        1900-01-05

我想将结果显示为

Pro_Id  RMC2    RMC2ExpeDt   RMC2ComDt  RMC1    RMC1ExpeDt  RMC1ComDt 
1         RMC2     1900-01-01    1900-01-05 RMC1    1999-05-05    1999-05-06
2         NULL      NULL             NULL   RMC1    2010-06-05    2010-07-06

下面是我的查询，返回'

Below is my query which returns'

DECLARE @SQL1 NVARCHAR(MAX) = ''
DECLARE @SQL NVARCHAR(MAX) = ''

SELECT  @SQL1 = STUFF((SELECT ',' + QUOTENAME(Type_Ds) + ',' +  QUOTENAME(Type_Ds + ' Expected Date') + ',' + QUOTENAME(Type_Ds + ' Completed Date')
                    from dbo.AppType 
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')         
        print @SQL1

SET @SQL = 'SELECT  *
            FROM    (   SELECT A.Pro_Id,
                                Type_DS
                        FROM  dbo.Project A  left join [dbo].[Prj_App] B on A.Pro_id = B.Pro_Id
                         right outer join dbo.AppType C on B.App_Id = C.App_ID
                    ) data
                    PIVOT
                    (   MAX(Type_DS)
                        FOR Type_DS IN (' + @SQL1 + ') 
                    ) pvt1                                        
                    where Pro_Id is not null'

print @SQL
EXECUTE SP_EXECUTESQL @SQL

Pro_Id  RMC2    RMC2ExpeDt   RMC2ComDt  RMC1    RMC1ExpeDt  RMC1ComDt 
1   RMC2    NULL     NULL             RMC1  NULL     NULL
2   NULL    NULL     NULL             RMC1  NULL     NULL.

任何人都可以帮忙...

can anyone help on it...

推荐答案

在使用动态SQL时，我的建议是始终先编写硬编码的查询，这样您才能获得正确的逻辑，然后将其转换为动态逻辑SQL.

My suggestion when you are working with dynamic SQL is to always write the query hard-coded first, so you can get the logic correct, then convert it to dynamic SQL.

由于您尝试透视3列数据，因此我将首先取消透视type_ds，expdt和comdt`列，然后应用PIVOT函数.

Since you are attempting to pivot 3 columns of data I would first unpivot the type_ds, expdt and comdt` columns, then apply the PIVOT function.

查询的硬编码版本为:

SELECT  *
FROM    
(   
  select pro_id,
    type_ds = case 
                when col ='type_ds' 
                then type_ds 
                else type_ds+col end,
    value
  from
  (
     SELECT A.Pro_Id,
       c.Type_DS,
       convert(varchar(10), b.ExpDt, 120) ExpDt, 
       convert(varchar(10), b.ComDt, 120) ComDt
     FROM  dbo.Project A  
     left join [dbo].[Prj_App] B 
        on A.Pro_id = B.Pro_Id
     right outer join dbo.Approval_Type C 
        on B.App_Id = C.App_ID
   ) s
   cross apply
   (
      select 'type_ds', type_ds union all
      select 'expdt', expdt union all
      select 'comdt', comdt
   ) c (col, value)
) data
PIVOT
(   
  MAX(value)
  FOR Type_DS IN (RMC2, RMC2expdt, RMC2comdt,
                   RMC1, RMC1expdt, RMC1comdt) 
) pvt1                                        

请参见带演示的SQL提琴.现在有了查询的工作版本，您可以轻松地将其转换为动态SQL:

See SQL Fiddle with Demo. Now that you have a working version of the query, you can easily convert it to dynamic SQL:

DECLARE @SQL1 NVARCHAR(MAX) = ''
DECLARE @SQL NVARCHAR(MAX) = ''

SELECT  @SQL1 = STUFF((SELECT ',' + QUOTENAME(Type_Ds) + ',' +  QUOTENAME(Type_Ds + 'ExpDt') + ',' + QUOTENAME(Type_Ds + 'ComDt')
                    from dbo.Approval_Type 
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')   


SET @SQL = 'SELECT  *
            FROM    
            (   
              select pro_id,
                type_ds = case 
                            when col =''type_ds'' 
                            then type_ds 
                            else type_ds+col end,
                value
              from
              (
                 SELECT A.Pro_Id,
                   c.Type_DS,
                   convert(varchar(10), b.ExpDt, 120) ExpDt, 
                   convert(varchar(10), b.ComDt, 120) ComDt
                 FROM  dbo.Project A  
                 left join [dbo].[Prj_App] B 
                    on A.Pro_id = B.Pro_Id
                 right outer join dbo.Approval_Type C 
                    on B.App_Id = C.App_ID
               ) s
               cross apply
               (
                  select ''type_ds'', type_ds union all
                  select ''expdt'', expdt union all
                  select ''comdt'', comdt
               ) c (col, value)
           ) data
           PIVOT
           (   
              MAX(value)
              FOR Type_DS IN (' + @SQL1 + ') 
           ) pvt1 '

--print @SQL
EXECUTE SP_EXECUTESQL @SQL

请参见带有演示的SQL提琴

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