How to create a PivotTable in Transact/SQL?
You need to use a PIVOT. You can use either a STATIC PIVOT where you know the values of the columns to transform or a DYNAMIC PIVOT where the columns are unknown until execution time.
Static Pivot (See SQL Fiddle with Demo):
select *
from
(
select memid, Condition_id, Condition_Result
from t
) x
pivot
(
sum(condition_result)
for condition_id in ([C1], [C2], [C3], [C4])
) p
Dynamic Pivot (See SQL Fiddle with Demo):
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.condition_id)
FROM t c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT memid, ' + @cols + ' from
(
select MemId, Condition_id, condition_result
from t
) x
pivot
(
sum(condition_result)
for condition_id in (' + @cols + ')
) p '
execute(@query)
Both will generate the same results.
Creating a complex pivot table in T-SQL
You can get there by pivoting on Month and Year and then using WITH ROLLUP
WITH data
AS (SELECT *
FROM (SELECT Dateadd(month, Datediff(month, 0, PCT.dateofdelivery), 0
)
MonthYear,
PCT.unitid,
PCT.unitname
AS
[Unit],
PCT.casedefinition
AS
[Case definition]
FROM pivotbase AS PCT) AS P1
PIVOT ( Count(unitid)
FOR [case definition] IN ([Case type 1],
[Case type 2]) ) AS p2),
rollup
AS (SELECT Month(monthyear) Month,
unit unitX,
Year(monthyear) Year,
Sum([case type 1]) [case type 1],
Sum([case type 2]) [case type 2],
Grouping(unit) GUnit,
Grouping(Month(monthyear)) gm,
Grouping(Year(monthyear)) gy
FROM data
GROUP BY unit,
Year(monthyear),
Month(monthyear) WITH rollup)
SELECT COALESCE(Cast(month AS VARCHAR), Cast(year AS VARCHAR), unitx,
'Grand Total')
Unit,
[case type 1],
[case type 2]
FROM rollup
ORDER BY gunit,
unitx,
year,
gm DESC,
month
SQLFiddle
Convert Rows to columns using 'Pivot' in SQL Server
If you are using SQL Server 2005+, then you can use the PIVOT function to transform the data from rows into columns.
It sounds like you will need to use dynamic sql if the weeks are unknown but it is easier to see the correct code using a hard-coded version initially.
First up, here are some quick table definitions and data for use:
CREATE TABLE yt
(
[Store] int,
[Week] int,
[xCount] int
);
INSERT INTO yt
(
[Store],
[Week], [xCount]
)
VALUES
(102, 1, 96),
(101, 1, 138),
(105, 1, 37),
(109, 1, 59),
(101, 2, 282),
(102, 2, 212),
(105, 2, 78),
(109, 2, 97),
(105, 3, 60),
(102, 3, 123),
(101, 3, 220),
(109, 3, 87);
If your values are known, then you will hard-code the query:
select *
from
(
select store, week, xCount
from yt
) src
pivot
(
sum(xcount)
for week in ([1], [2], [3])
) piv;
See SQL Demo
Then if you need to generate the week number dynamically, your code will be:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(Week)
from yt
group by Week
order by Week
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT store,' + @cols + ' from
(
select store, week, xCount
from yt
) x
pivot
(
sum(xCount)
for week in (' + @cols + ')
) p '
execute(@query);
See SQL Demo.
The dynamic version, generates the list of week numbers that should be converted to columns. Both give the same result:
| STORE | 1 | 2 | 3 |
---------------------------
| 101 | 138 | 282 | 220 |
| 102 | 96 | 212 | 123 |
| 105 | 37 | 78 | 60 |
| 109 | 59 | 97 | 87 |
How to Pivot table in SQL Server?
SELECT
Gdb
, Tbl
, EditDate
, [I]
, [U]
, [D]
FROM #TABLE
PIVOT (
COUNT(EditType)
FOR EditType IN ([U], [I], [D])
) AS PIVOTED
Create Pivot query in SQL Server like Excel pivot table
You can try to use CASE WHEN and SUM function to make it.
SELECT Brand,
SUM(CASE WHEN Location = 'Austria' THEN qty END) 'Austria_qty',
SUM(CASE WHEN Location = 'Austria' THEN Price END) 'Austria_totle',
SUM(CASE WHEN Location = 'France' THEN qty END) 'France_qty',
SUM(CASE WHEN Location = 'France' THEN Price END)'France_totle',
SUM(CASE WHEN Location = 'Germany' THEN qty END) 'Germany_qty',
SUM(CASE WHEN Location = 'Germany' THEN Price END)'Germany_totle',
SUM(CASE WHEN Location = 'Italy' THEN qty END) 'Italy_qty',
SUM(CASE WHEN Location = 'Italy' THEN Price END) 'Italy_totle'
FROM T
GROUP BY Brand
sqlfiddle:http://sqlfiddle.com/#!18/90e75/17
Results:
| Brand | Austria_qty | Austria_totle | France_qty | France_totle | Germany_qty | Germany_totle | Italy_qty | Italy_totle |
|---------|-------------|--------------------|------------|--------------|-------------|---------------|-----------|-------------|
| Apple | 1 | 1351.16 | 1 | 9.96 | 2 | 1583.85 | 1 | 1053.83 |
| Huawei | 1 | 744.67 | (null) | (null) | 2 | 207704.86 | (null) | (null) |
| Lenovo | 2 | 1184.21 | 2 | 1420.43 | 2 | 3454.91 | (null) | (null) |
| Nokia | (null) | (null) | 1 | 796.03 | (null) | (null) | 1 | 538.41 |
| Samsung | (null) | (null) | 1 | 3327.14 | (null) | (null) | 1 | 9.09 |
Edit
I saw your commit, if you want to get Total that you can try to use with ROLLUP
SELECT COALESCE(Brand, 'Total') Brand,
SUM(CASE WHEN Location = 'Austria' THEN qty END) 'Austria_qty',
SUM(CASE WHEN Location = 'Austria' THEN Price END) 'Austria_totle',
SUM(CASE WHEN Location = 'France' THEN qty END) 'France_qty',
SUM(CASE WHEN Location = 'France' THEN Price END)'France_totle',
SUM(CASE WHEN Location = 'Germany' THEN qty END) 'Germany_qty',
SUM(CASE WHEN Location = 'Germany' THEN Price END)'Germany_totle',
SUM(CASE WHEN Location = 'Italy' THEN qty END) 'Italy_qty',
SUM(CASE WHEN Location = 'Italy' THEN Price END) 'Italy_totle'
FROM T
GROUP BY Brand with ROLLUP
sqlfiddle
Result
| Brand | Austria_qty | Austria_totle | France_qty | France_totle | Germany_qty | Germany_totle | Italy_qty | Italy_totle |
|---------|-------------|--------------------|------------|--------------------|-------------|---------------|-----------|--------------------|
| Apple | 1 | 1351.16 | 1 | 9.96 | 2 | 1583.85 | 1 | 1053.83 |
| Huawei | 1 | 744.67 | (null) | (null) | 2 | 207704.86 | (null) | (null) |
| Lenovo | 2 | 1184.2099999999998 | 2 | 1420.43 | 2 | 3454.91 | (null) | (null) |
| Nokia | (null) | (null) | 1 | 796.03 | (null) | (null) | 1 | 538.41 |
| Samsung | (null) | (null) | 1 | 3327.14 | (null) | (null) | 1 | 9.09 |
| Totle | 4 | 3280.04 | 5 | 5553.5599999999995 | 6 | 212743.62 | 3 | 1601.3299999999997 |
SQL Server: how to create a table from a pivot table?
You can create it on the fly with INTO
SELECT * INTO #TAB
FROM
(
SELECT *
FROM TABLE1
) A
PIVOT
(
AVG(COL3)
FOR COL2 IN ([A],[B],[C],[D])
) AS PV;
With Schema provided by Chanukya. (Worked for me)
CREATE TABLE #D
(
COL1 INT,
COL2 VARCHAR(10),
COL3 INT
)
INSERT INTO #D VALUES
(1,'A',11),
(2,'B',22),
(3,'C',33),
(4,'D',44)
SELECT * INTO #PIVOT_RESULT
FROM (
SELECT COL1, COL2, COL3 FROM #D
)AS A
PIVOT
(
AVG(COL3)
FOR COL2 IN ([A],[B],[C],[D])
) AS PV;
SELECT * FROM #PIVOT_RESULT
Understanding PIVOT function in T-SQL
A PIVOT used to rotate the data from one column into multiple columns.
For your example here is a STATIC Pivot meaning you hard code the columns that you want to rotate:
create table temp
(
id int,
teamid int,
userid int,
elementid int,
phaseid int,
effort decimal(10, 5)
)
insert into temp values (1,1,1,3,5,6.74)
insert into temp values (2,1,1,3,6,8.25)
insert into temp values (3,1,1,4,1,2.23)
insert into temp values (4,1,1,4,5,6.8)
insert into temp values (5,1,1,4,6,1.5)
select elementid
, [1] as phaseid1
, [5] as phaseid5
, [6] as phaseid6
from
(
select elementid, phaseid, effort
from temp
) x
pivot
(
max(effort)
for phaseid in([1], [5], [6])
)p
Here is a SQL Demo with a working version.
This can also be done through a dynamic PIVOT where you create the list of columns dynamically and perform the PIVOT.
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.phaseid)
FROM temp c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT elementid, ' + @cols + ' from
(
select elementid, phaseid, effort
from temp
) x
pivot
(
max(effort)
for phaseid in (' + @cols + ')
) p '
execute(@query)
The results for both:
ELEMENTID PHASEID1 PHASEID5 PHASEID6
3 Null 6.74 8.25
4 2.23 6.8 1.5
Related Topics
What's the Proper Index for Querying Structures in Arrays in Postgres JSONb
What Does Sp_Reset_Connection Do
SQL Error: Ora-01861: Literal Does Not Match Format String 01861
How to Create Table Using Select Query in SQL Server
SQL Server Replace, Remove All After Certain Character
Does Ms SQL Server's "Between" Include the Range Boundaries
SQL Server Ignore Case in a Where Expression
Oracle -- Split Multiple Comma Separated Values in Oracle Table to Multiple Rows
SQL Server Convert Integer to Binary String
Postgresql Equivalent for Top N with Ties: Limit "With Ties"
Which SQL Query Is Better, Match Against or Like
Update One Table with Data from Another
Concatenate Results from a SQL Query in Oracle
Compute Percents from Sum() in the Same Select SQL Query
Why Is a Udf So Much Slower Than a Subquery
How to Select Id with Max Date Group by Category in Postgresql