Sql枢轴表

Question

这是我的一号桌

BId GId    Title   Limit
 1   1    Optical    10
 2   1    Dental      5
 3   1    Massage     4

这是表2

SId  BId  Include  ServiceTitle  LimitApply
 1    1     True      Optical        False
 2    2     False     Dental         True
 3    3     False     Massage        False

我要我的最后一张桌子像下面这样。

BId Title    Limit Optical-Include Optical-LimitApply  Dental-Include Dental-LimitApply Massage-Include Massage-LimitApply
 1  Optical   10       True             False
 2  Dental    5                                            False             True
 3  Massage   4                                                                               False                True 

我对sql很陌生，我正在尝试创建枢轴表，但我不知道如何获得结果表。

WITH Sales AS (
 SELECT
    S.BId,
    S.Title,
    I.Include,
    I.ServiceTitle,
    I.LimitApply
    FROM
     dbo.BenefitLimit S
     INNER JOIN dbo.ServicesCombined I
     ON S.BId = I.BId
)
SELECT * FROM Sales
   PIVOT (Max(Include) FOR Include IN (Optical, Dental, Massage)) P

我没有得到我想要的结果。我有50,000多份BId记录。任何帮助都将不胜感激。

Answer 1

万一你需要动感

Declare @SQL varchar(max) = Stuff((Select Distinct ',' + QuoteName(ServiceTitle+'-Include')+',' + QuoteName(ServiceTitle+'-LimitApply') From Table2 Order by 1 For XML Path('')),1,1,'') 
Select  @SQL = '
 Select [BId],[Title],[Limit],' + @SQL + '
  From (
        Select A.BId
              ,A.Title
              ,A.Limit
              ,C.Item
              ,C.Value
         From  Table1 A 
         Join  Table2 B on A.BId = B.BId
         Cross Apply (
                      Select Item = ServiceTitle+''-Include'',Value = Include
                      Union All
                      Select Item = ServiceTitle+''-LimitApply'',Value = LimitApply
                     ) C
       ) A
 Pivot (Max(Value) For [Item] in (' + @SQL + ') ) p'
Exec(@SQL);

返回

​

​

Answer 2

你可以试试这个：

 SELECT 
          S.BId,
          S.Title,
          S.Limit,
          I.ServiceTitle as CONCAT(S.Title, '-Include'),
          I.LimitApply as CONCAT(S.Title, '-LimitApply'),


    FROM    S,I where S.BID=I.BID ORDER BY S.BID

但我没有使用枢轴。

Answer 3

使用动态数据透视查询：

            DECLARE @SQL AS VARCHAR(MAX)
            DECLARE @Columns1 AS VARCHAR (MAX)
            DECLARE @Columns2 AS VARCHAR (MAX)

            SELECT @Columns1 =COALESCE(@Columns1 + ', ','')+ QUOTENAME(Title1)
            FROM
            (
                        select 
                        Title + '-Include' as Title1 
                        from  BenefitLimit S 
                        INNER JOIN  ServicesCombined I
                        ON S.BId = I.BId 
            ) AS B


            SELECT @Columns2 =COALESCE(@Columns2 + ', ','')+ QUOTENAME(Title2)
            FROM
            (
                        select 
                        Title + '-LimitApply' as Title2 
                        from  BenefitLimit S 
                        INNER JOIN  ServicesCombined I
                        ON S.BId = I.BId 
            ) AS B


            SET @SQL = '
                        SELECT *
                        FROM (
                        select 
                        S.BId, 
                        Limit,Title,
                        Title + ''-Include'' as Title1 ,
                        Include,
                        Title + ''-LimitApply'' as Title2,
                        LimitApply 
                        from  BenefitLimit S 
                        INNER JOIN  ServicesCombined I
                        ON S.BId = I.BId  )A
                         Pivot
                         (

                         max (Include)  FOR Title1 IN (' + @Columns1 + ')
                         )  as p1
                          Pivot
                         (

                         max (LimitApply)  FOR Title2 IN (' + @Columns2 + ')
                         )  
                         as p2
            '            
            EXEC  (@SQL)

检查这里，演示