Sql pivot tables

Question

This is my table 1

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

This is table 2

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

I want my final table like below.

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 

I am new to sql and I am trying to create pivot tables but I am not sure how to get the result table.

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

I am not getting the result what i want. I have more than 50,000 BId records. Any help would be appreciated.

Answer 1

   ;With Sales AS 
    (
        Select  D1.BId, D1.Title, D1.Limit , 
       cast(Cast(ISNULL(D2.[Include],0) as bit)AS TINYINT) as [Include],
        D2.ServiceTitle,
        cast(Cast(ISNULL(D2.LimitApply,0) as bit)AS TINYINT) as LimitApply
        FROM        table1 D1
        Inner Join  table2 D2
             ON     D1.BId = D2.BId
    ), 
    Tb_Includ as
    (
    SELECT * FROM Sales
       PIVOT (Max([Include]) FOR ServiceTitle IN (Optical, Dental, Massage)) P
    ),
    Tb_LimitApply as
    (
    SELECT * FROM Sales
       PIVOT (Max(LimitApply)   FOR ServiceTitle  IN (Optical, Dental, Massage)) P
    )
    Select      D1.BId, D1.Title, D1.LimitApply, D1.Limit, D1.Optical as Optical_Include, D1.Dental as Dental_Include, D1.Massage as Massage_Include,
                D2.Optical as Optical_LimitApply, D2.Dental as Dental_LimitApply, D2.Massage as Massage_LimitApply
    From        Tb_Includ D1
    Inner join  Tb_LimitApply D2
        On      D1.BId = D2.BId

Answer 2

Using Dynamic Pivot Query :

            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)

Check here Demo

Answer 3

In case you need to go Dynamic

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);

Returns

Answer 4

You may try this:

 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

Yet I didn't use pivot.