SQL Server Join Tables and Pivot

SQL server join tables and pivot

This should work:

WITH Sales AS (
   SELECT
      S.SaleID,
      S.SoldBy,
      S.SalePrice,
      S.Margin,
      S.Date,
      I.SalePrice,
      I.Category
   FROM
      dbo.Sale S
      INNER JOIN dbo.SaleItem I
         ON S.SaleID = I.SaleID
)
SELECT *
FROM
   Sales
   PIVOT (Max(SalePrice) FOR Category IN (Books, Printing, DVD)) P
;

Or alternately:

SELECT
   S.SaleID,
   S.SoldBy,
   S.SalePrice,
   S.Margin,
   S.Date,
   I.Books,
   I.Printing,
   I.DVD
FROM
   dbo.Sale S
   INNER JOIN (
      SELECT *
      FROM
         (SELECT SaleID, SalePrice, Category FROM dbo.SaleItem) I
         PIVOT (Max(SalePrice) FOR Category IN (Books, Printing, DVD)) P
   ) I ON S.SaleID = I.SaleID
;

These have the same result set and may in fact be treated the same by the query optimizer, but possibly not. The big difference comes into play when you start putting conditions on the Sale table—you should test and see which query works better.

Note: it is crucial when using PIVOT that only the columns that should be part of the resulting output are available. This is why the two above queries have extra derived table subqueries (SELECT ...) so that only specific columns are exposed. All columns that are available to be seen by PIVOT that aren't listed in the pivot expression will implicitly be grouped on and included in the final output. This will likely not be what you want.

May I suggest, however, that you do the pivoting in the presentation layer? If, for example, you are using SSRS it is quite easy to use a matrix control that will do all the pivoting for you. That is best, because then if you add a new Category, you won't have to modify all your SQL code!

There is a way to dynamically find the column names to pivot, but it involves dynamic SQL. I don't really recommend that as the best way, either, though it is possible.

Another way that could work would be to preprocess this query—meaning to set a trigger on the Category table that rewrites a view to contain all the extant categories that exist. This does solve a lot of the other problems I've mentioned, but again, using the presentation layer is best.

Note: If your column names (that were formerly values) have spaces, are numbers or begin with a number, or are otherwise not valid identifiers, you must quote them with square brackets as in PIVOT (Max(Value) FOR CategoryId IN ([1], [2], [3], [4])) P. Alternately, you can modify the values before they get to the PIVOT part of the query to prepend some letters or remove spaces, so that the column list doesn't need escaping. For further reading on this check out the rules for identifiers in SQL Server.

Join two tables, group and pivot in SQL

You want to unpivot. In SQL Server I would recommend cross apply:

select t1.id, t1.date, x.field, x.pivot, t2.sku
from table1 t1
inner join table2 t2 on t2.id = t1.id and t1.date = t2.date
cross apply (values (t1.avail, 'avail'), (t1.total - t1.avail, 'used')) as x(field, pivot)

It is not clear which logic you want for column group (which, by the way, is a language keyword, so not a good choice for a column name). Based on your attempt, it looks like you want to assign that based on the sku. If so, use a case expression:

select t1.id, t1.date, x.field, x.pivot, t2.sku,
    case 
        when t2.sku like 'Ax%' or t2.sku like 'At%' then 'group1'
        when t2.sku like 'By%' or t2.sku like 'Bt%' then 'group2'
    end as grp
from table1 t1
inner join table2 t2 on t2.id = t1.id and t1.date = t2.date
cross apply (values (t1.avail, 'avail'), (t1.total - t1.avail, 'used')) as x(field, pivot)

Using PIVOT and JOIN together

WITH T
     AS (SELECT [Order Details].OrderID,
                c.CategoryName,
                c.CategoryID
         FROM   [Order Details]
                INNER JOIN Products p
                  ON p.ProductID = [Order Details].ProductID
                INNER JOIN Categories c
                  ON c.CategoryID = p.CategoryID)
SELECT *
FROM   T PIVOT ( COUNT (CategoryID) FOR CategoryName IN ( 
       [Beverages],
       [Condiments],
       [Confections], 
       [Dairy Products], 
       [Grains/Cereals],
       [Meat/Poultry],
       [Produce],
       [Seafood]) ) AS pvt
ORDER  BY OrderID  

SQL SERVER PIVOT table with joins and dynamic columns

Since you want to transform data from rows into columns, then you will want to use the PIVOT function. If you have a limited number or known values, then you can hard-code the query:

select plan_id, [2012, November], [2012, December], [2013, January], [2013, February]
from
(
    SELECT 
        b.plan_id, 
        (Convert(varchar(4),b.run_year) + ', ' +  DateName(month,CAST('1900-' + Convert(varchar(2),b.run_month) + '-01' AS DATETIME))) AS billdate, 
        ISNULL(b.total_premium,0) + ISNULL(a.total_adj,0) AS total
    FROM cteBills b
    LEFT JOIN cteBillsAdj a 
        ON a.run_month = b.run_month 
        AND b.run_year = a.run_year 
        AND b.plan_id = a.plan_id
) d
pivot
(
    sum(total)
    for billdate in ([2012, November], [2012, December], [2013, January], [2013, February])
) piv;

But if you have an unknown number of values, then you will need to implement dynamic SQL:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT  ',' + QUOTENAME(Convert(varchar(4),b.run_year) + ', ' +  DateName(month,CAST('1900-' + Convert(varchar(2),b.run_month) + '-01' AS DATETIME))) ) 
                    from cteBills
                    group by b.run_year, b.run_month
                    order by b.run_year, b.run_month
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT plan_id, ' + @cols + ' 
            from 
             (
                SELECT 
                    b.plan_id, 
                    (Convert(varchar(4),b.run_year) + '', '' +  DateName(month,CAST(''1900-'' + Convert(varchar(2),b.run_month) + ''-01'' AS DATETIME))) AS billdate, 
                    ISNULL(b.total_premium,0) + ISNULL(a.total_adj,0) AS total
                FROM cteBills b
                LEFT JOIN cteBillsAdj a 
                    ON a.run_month = b.run_month 
                    AND b.run_year = a.run_year 
                    AND b.plan_id = a.plan_id
            ) x
            pivot 
            (
                sum(total)
                for billdate in (' + @cols + ')
            ) p '

execute sp_executesql @query;

SQL PIvot for a complex join

You can do it with a CROSS join of the tables and conditional aggregation:

SELECT e.EMPLOYEEID
FROM managers m CROSS JOIN employees e
WHERE m.MANAGERID = 1
GROUP BY e.EMPLOYEEID
HAVING COUNT(DISTINCT m."GROUP") = 
       COUNT(CASE WHEN e."GROUP" = m."GROUP" AND  e."VALUE" = m."VALUE" THEN 1 END)

See the demo.

Results:

CREATE TABLE #myTable (
[Year] int, [Type] int, [Value] int, [ExtraColumn] varchar(10));

INSERT INTO #myTable ([Year], [Type], [Value], [ExtraColumn]) 
VALUES (2010, 1, 10, 'I'), 
       (2010, 2, 15, 'G'), 
       (2010, 3, 20, 'N'), 
       (2011, 1, 100, 'O'), 
       (2011, 2, 150, 'R'), 
       (2011, 3, 200, 'E'); 

select Year, [1] as Type1, [2] as Type2, [3] as Type3 
from ( 
 select [Year], [Type], [Value] 
 from #myTable 
 ) t 
PIVOT ( SUM(Value) FOR [Type] IN ( [1], [2], [3] ) ) pvt;

-- OR
with myData as
( 
 select [Year], [Type], [Value] 
 from #myTable 
)
select Year, [1] as Type1, [2] as Type2, [3] as Type3 
from myData
PIVOT ( SUM(Value) FOR [Type] IN ( [1], [2], [3] ) ) pvt;

drop table #myTable;