Sql Server Pivot with Multiple X-Axis Columns

SQL Server PIVOT with multiple X-axis columns

Ok, as you said, you are gonna need dynamic SQL, so first go to this link. Once you read that, try the following:

UPDATED CODE FOLLOWING COMMENT:

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

WITH CTE AS
(
    SELECT *, CAST([Year] AS NVARCHAR(4))+RIGHT('00'+CAST([Month] AS NVARCHAR(2)),2) YearMonth
    FROM YourTable
)

SELECT @cols = STUFF((  SELECT DISTINCT ',' + QUOTENAME(YearMonth) 
                        FROM CTE 
                        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,''),
       @cols2 = STUFF(( SELECT DISTINCT ',ISNULL(' + QUOTENAME(YearMonth) + ',0) AS ' + QUOTENAME(YearMonth)
                        FROM CTE 
                        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')

SET @query = '
SELECT Payroll, Forname, Surname, ' + @cols2 + '
FROM (  SELECT  Payroll, Forname, Surname, 
                CAST([Year] AS NVARCHAR(4))+RIGHT(''00''+CAST([Month] AS NVARCHAR(2)),2) YearMonth,
                Amount
        FROM YourTable ) T
PIVOT(SUM(Amount) FOR YearMonth IN ('+@cols+')) PT'

EXEC(@Query)

2 X-Axis row, 1 Y-Axis Column and 2 Z-Axis Value Pivot Table SQL Server 2008 query

yes. it is possible.

SELECT  consignee , shipper ,
        jan_cm1 = MAX (CASE WHEN month = 'Jan' THEN cm1 END),
        jan_lss = MAX (CASE WHEN month = 'Jan' THEN lss END),
        feb_cm1 = MAX (CASE WHEN month = 'Feb' THEN cm1 END),
        feb_lss = MAX (CASE WHEN month = 'Feb' THEN lss END)
FROM    yourtable
GROUP BY consignee , shipper 

SQL query with pivot tables?

You might try it like this

Here I set up a MCVE, please try to create this in your next question yourself...

DECLARE @Name TABLE (pName VARCHAR(100));
INSERT INTO @Name VALUES('John'),('Joe'),('Jack'),('Jane');
DECLARE @Group TABLE(gName VARCHAR(100),gID INT);
INSERT INTO @Group VALUES ('Soccer',1),('Hockey',2),('Basketball',3);
DECLARE @map TABLE(pName VARCHAR(100),gID INT);
INSERT INTO @map VALUES
  ('John',1),('John',3)
 ,('Joe',2)
 ,('Jack',1),('Jack',2),('Jack',3)
 ,('Jane',3);

This quer will collect the values and perform PIVOT

 SELECT p.*
 FROM
 (
     SELECT n.pName
           ,g.gName
           ,'x' AS IsInGroup
     FROM @map AS m
     INNER JOIN @Name AS n ON m.pName=n.pName
     INNER JOIN @Group AS g ON m.gID=g.gID
 ) AS x
 PIVOT
 (
    MAX(IsInGroup) FOR gName IN(Soccer,Hockey,Basketball)
 ) as p 

This is the result.

pName   Soccer  Hockey  Basketball
Jack    x       x       x
Jane    NULL    NULL    x
Joe     NULL    x       NULL
John    x       NULL    x

Some hints:

• You might use 1 and 0 instead of x as SQL Server does not know a real boolean
• You should add a pID to your names. Never join tables on real data (unless it is something unique and unchangeable [which means never acutally!!!])

UPDATE dynamic SQL (thx to @djlauk)

If you want a query which deals with any amount of groups you have to to this dynamically. But please be aware, that you loose the chance to use this in ad-hoc-SQL like in VIEW or inline TVF, which is quite a big backdraw...

CREATE TABLE #Name(pName VARCHAR(100));
INSERT INTO #Name VALUES('John'),('Joe'),('Jack'),('Jane');
CREATE TABLE #Group(gName VARCHAR(100),gID INT);
INSERT INTO #Group VALUES ('Soccer',1),('Hockey',2),('Basketball',3);
CREATE TABLE #map(pName VARCHAR(100),gID INT);
INSERT INTO #map VALUES
  ('John',1),('John',3)
 ,('Joe',2)
 ,('Jack',1),('Jack',2),('Jack',3)
 ,('Jane',3);

DECLARE @ListOfGroups VARCHAR(MAX)=
(
    STUFF
    (
        (
        SELECT DISTINCT ',' + QUOTENAME(gName) 
        FROM #Group
        FOR XML PATH('')
        ),1,1,''
    )
);

DECLARE @sql VARCHAR(MAX)=
(
 'SELECT p.*
 FROM
 (
     SELECT n.pName
           ,g.gName
           ,''x'' AS IsInGroup
     FROM #map AS m
     INNER JOIN #Name AS n ON m.pName=n.pName
     INNER JOIN #Group AS g ON m.gID=g.gID
 ) AS x
 PIVOT
 (
    MAX(IsInGroup) FOR gName IN(' +  @ListOfGroups + ')
 ) as p');

 EXEC(@sql); 
 GO

 DROP TABLE #map;
 DROP TABLE #Group;
 DROP TABLE #Name;

SQL - Pivot table from a multilayer SELECT statement

Irrespective of the complexity of your query, you can wrap it and then PIVOT it as follows, provided that the columns being pivoted match those returned from the inner select. I've assumed a SUM aggregation on TAKSIT

SELECT *
FROM
(
  SELECT * 
   FROM 
   YourReallyComplexQueryHere
) x
PIVOT
(
  SUM(TAKSIT)
  FOR CURCODE IN ([USD], [EUR], [YEN])
) xx;

SqlFiddle here

How do I separate query results into columns where data from each month is represented?

Use this code:

IF OBJECT_ID('tempdb..#UsingColor','U') IS NOT NULL DROP TABLE #UsingColor;

CREATE TABLE #UsingColor
(color VARCHAR(10) NOT NULL
, usingDate DATE NOT NULL)

INSERT INTO #UsingColor(color, usingDate)
VALUES('red', '20160101')
, ('green', '20160101')
, ('blue', '20160201')
, ('red','20160201')
, ('red', '20160301')
, ('green', '20160301')
, ('blue', '20160301')
, ('orange','20160301')
, ('green', '20160401')
, ('green', '20160401')
, ('blue', '20160401')
, ('orange','20160401')
, ('blue', '20160401')
, ('green', '20160401')
, ('white', '20160401')
, ('orange','20160401')
, ('green', '20160501')
, ('white', '20160501')
, ('orange','20160601')
, ('white', '20160601')
, ('orange','20160601')
, ('green', '20160701')
, ('blue', '20160701')
, ('red','20160701')
, ('red', '20160801')
, ('green', '20160801')
, ('blue', '20160801')
, ('orange','20160801')
, ('green', '20160901')
, ('green', '20160901')
, ('blue', '20160901')
, ('orange','20160901')
, ('blue', '20160901')
, ('green', '20160901')
, ('white', '20160901')
, ('orange','20160901')
, ('green', '20161001')
, ('white', '20161001')
, ('orange','20161101')
, ('white', '20161101')
, ('orange','20161101')
--, ('black', '20161201')

SELECT color
    , [1] AS Jan
    , [2] AS Feb
    , [3] AS Mar
    , [4] AS Apr
    , [5] AS May
    , [6] AS Jun
    , [7] AS Jul
    , [8] AS Aug
    , [9] AS Sep
    , [10] AS Oct
    , [11] AS Nov
    , [12] AS Dec
FROM(
SELECT color, 1 AS cntr, MONTH(usingDate) AS m FROM #UsingColor) AS D
PIVOT(COUNT(cntr) FOR m IN([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])) AS P

Using PIVOT with SQL Server without Aggregate function

You need to "FEED" the pivot with an X-Axis,Y-Axis and a Value. We create a row key via dense_rank()

Example

Declare @YourTable Table ([Name] varchar(50),[Occupation] varchar(50))  Insert Into @YourTable Values 
 ('Samantha','Doctor')
,('Julia','Actor')
,('Maria','Actor')
,('Meera','Singer')
,('Ashley','Professor')
,('Ketty','Professor')
,('Christeen','Professor')
,('Jane','Actor')
,('Jenny','Doctor')
,('Priya','Singer')

Select * 
 from (Select *
             ,RN = dense_rank() over (partition by occupation order by name)
        From  @YourTable
      ) src
 Pivot (max(Name) for Occupation in ([Doctor], [Professor],[Singer], [Actor]) ) pvt 

Returns

RN  Doctor      Professor   Singer  Actor
1   Jenny       Ashley      Meera   Jane
2   Samantha    Christeen   Priya   Julia
3   NULL        Ketty       NULL    Maria

NOTE:

If you don't want RN in your results, rather than the top SELECT *, you can specify the desired columns

SELECT [Doctor], [Professor],[Singer], [Actor]
 From  (...) src
 Pivot (...) pvt

EDIT - Commentary

If you run the inner query

Select *
      ,RN = dense_rank() over (partition by occupation order by name)
 From  @YourTable
 Order By RN

You'll get

Name        Occupation  RN
Jane        Actor       1
Jenny       Doctor      1
Ashley      Professor   1
Meera       Singer      1
Priya       Singer      2
Christeen   Professor   2
Samantha    Doctor      2
Julia       Actor       2
Maria       Actor       3
Ketty       Professor   3

RN becomes the Y-Axis, Occupation becomes the X-Axis and Name is the value.
Pivots by design are aggregates, therefore we just need a Y-Axis to perform the group by.

Pivot Query in MS Access

Consider:

TRANSFORM First(Data.Cat) AS FirstOfCat
SELECT Data.ID, Data.Name
FROM Data
GROUP BY Data.ID, Data.Name
PIVOT "Cat" & DCount("*","Data","ID=" & [ID] & " AND Cat<'" & [Cat] & "'")+1;

Or if there is a unique record identifier field - autonumber should serve:

TRANSFORM First(Data.Cat) AS FirstOfCat
SELECT Data.ID, Data.Name
FROM Data
GROUP BY Data.ID, Data.Name
PIVOT "Cat" & DCount("*","Data","ID=" & [ID] & " AND ID_PK<" & [ID_PK])+1;

---

Related Topics