Using PIVOT in SQL Server 2008
Actually, you'd be better off doing this in the client. Suppose you're using Reporting Services, get the data as per your first result set and display it using a Matrix, with author_id and review_id in the Row Group, question_id in the Column Group, and MAX(answer_id) in the middle.
A query is doable, but you'd need dynamic SQL right now.
...something like:
DECLARE @QuestionList nvarchar(max);
SELECT @QuestionList = STUFF(
(SELECT ', ' + quotename(question_id)
FROM YourTable
GROUP BY question_id
ORDER BY question_id
FOR XML PATH(''))
, 1, 2, '');
DECLARE @qry nvarchar(max);
SET @qry = '
SELECT author_id, review_id, ' + @QuestionList +
FROM (SELECT author_id, review_id, question_id, answer_id
FROM YourTable
)
PIVOT
(MAX(AnswerID) FOR question_id IN (' + @QuestionList + ')) pvt
ORDER BY author_id, review_id;';
exec sp_executesql @qry;
SQL Server 2008 R2: Dynamic pivot query
using PIVOT we can achieve this
DECLARE @sales table
(
SalesCountry varchar(20),
SalesState varchar(20),
SalesMan varchar(20)
);
insert into @sales values('USA','TEXAS','Mak');
insert into @sales values('USA','California','Sam');
insert into @sales values('Cannada','Alberta','John');
insert into @sales values('Cannada','Manitoba','John');
Select SalesCountry,MAX([1]) State1,MAX([2]) State2,MAX([Sales_1])[Sales_1],MAX([Sales_2])[Sales_2] FROM (
select
SalesCountry,
SalesState,
SalesMan,
ROW_NUMBER()OVER(PARTITION BY SalesCountry ORDER BY SalesCountry)RN ,
'Sales'+'_'+CAST(ROW_NUMBER()OVER(PARTITION BY SalesCountry ORDER BY SalesCountry) AS VARCHAR)RNN
FROM @sales
)T
PIVOT (MAX(SalesState) for RN IN ([1],[2]))P
PIVOT (MAX(SalesMan) for RNN IN ([Sales_1],[Sales_2]))PP
GROUP BY PP.SalesCountry
PIVOT operator not working in SQL Server 2008 R2
In the SELECT statement add escape [ and ] for the column names 2005 and 2006, like [2005], [2006] will solve the issue.
Sample execution with the given data:
DECLARE @GenderYearWise TABLE ([Year] INT, Gender VARCHAR (100), Total INT);
INSERT INTO @GenderYearWise ([Year], Gender, Total) VALUES
(2005, 'Female' , 374),
(2005, 'Male' ,1579),
(2006, 'Female' , 853),
(2006, 'Male' ,4769);
Select
Gender, [2005], [2006]
From
@GenderYearWise
PIVOT
(SUM(total)
FOR [Year] IN ([2005], [2006])
) AS PitvotTable
Result:
Gender 2005 2006
Female 374 853
Male 1579 4769
Also escape column name for the reserved keyword Year.
sql server 2008 pivot table
You made your attempt too complicated :).
Also, pick other names for your columns instead of sum, group and number, because not only those are sql-syntax keywords, it also makes the queries harder to read (e.g. sum([sum]), group by [group]).
drop table #temp
GO
select
*
into #temp
from (
select 'a' as [group],1 as [number],'-2503' as [sum],'WTH' as [source] union all
select 'a',2,-180,'DET' union all
select 'a',3,-156,'PLY' union all
select 'a',4,-99,'DET' union all
select 'a',5,-252,'DET'
) x
GO
select
[group], [number],
det, ply, wth
from #temp
pivot (
sum([sum]) for [source] in (det,ply,wth)
) x
Columns to Rows using SQL PIVOT... SQL Server 2008 R2
Based on your desired results it looks like you need to do an unpivot transform followed by a pivot, like this:
select
YEAR,
[Bps on Assets],[Setup Fee],[Account Min],[BAA Fees],[RedTail Fees (CRM)],
[RedTail Fees (Per User)],[External IT],[External IT Setup]
from (
select Expense, value, year
from SM_TechBundleExpnsRates
unpivot (
value FOR year IN ([Year1], [Year2], [Year3], [Year4], [Year5])
) up
) a
pivot (
sum(value) for expense in
(
[Bps on Assets],[Setup Fee],[Account Min],
[BAA Fees],[RedTail Fees (CRM)],
[RedTail Fees (Per User)],[External IT],[External IT Setup]
)
) p
Sample SQL Fiddle
Note that this isn't dynamic in any way, but rather uses hard coded column values for the years and expenses. It's possible to generate the code in a dynamic fashion - if you want to know how there are plenty of good answers showing how to do dynamic pivot with SQL Server.
Edit: did the dynamic version for fun, it might not be perfect but it should work:
DECLARE @sql AS NVARCHAR(MAX)
DECLARE @year_cols AS NVARCHAR(MAX)
DECLARE @expe_cols AS NVARCHAR(MAX)
SELECT @expe_cols= ISNULL(@expe_cols + ',','') + QUOTENAME(Expense)
FROM (SELECT DISTINCT Expense FROM SM_TechBundleExpnsRates) AS Expenses
SELECT @year_cols= ISNULL(@year_cols + ',','') + QUOTENAME(year)
FROM (
SELECT c.name AS year
FROM sys.tables t JOIN sys.columns c ON t.object_id = c.object_id
WHERE t.name = 'SM_TechBundleExpnsRates' AND c.name LIKE '%Year%'
) AS Years
SET @sql = N'
SELECT
Year, ' + @expe_cols + '
FROM (
SELECT Expense, Value, Year
FROM SM_TechBundleExpnsRates
UNPIVOT ( Value FOR Year IN (' + @year_cols + ') ) AS up
) a PIVOT ( SUM(Value) FOR Expense IN (' + @expe_cols + ') ) p'
EXEC sp_executesql @sql
SQL Server 2008 Pivot Query - Datetime
You can use pivoting like:
;WITH dtrTable AS ( --just a test sample of your table
SELECT *
FROM (VALUES
(1, 'emp1', '2016-10-20', '2016-10-20 10:00:00.000', '2016-10-20 15:00:00.000'),
(1, 'emp1', '2016-10-20', '2016-10-20 15:30:00.000', '2016-10-20 17:00:00.000'),
(1, 'emp1', '2016-10-20', '2016-10-20 18:30:00.000', '2016-10-20 19:00:00.000'),
(2, 'emp2', '2016-10-20', '2016-10-20 10:00:00.000', '2016-10-20 19:00:00.000'),
(2, 'emp2', '2016-10-20', '2016-10-20 21:00:00.000', '2016-10-20 22:00:00.000'),
(2, 'emp2', '2016-10-21', '2016-10-20 11:00:00.000', '2016-10-20 21:00:00.000')
) as t(empId, empName, dtrDate, dtrIn, dtrOut)
)
SELECT *
FROM (
SELECT empId,
empName,
dtrDate,
[Columns]+seq as [Columns],
[Values]
FROM (
SELECT *,
CAST(ROW_NUMBER() OVER (PARTITION BY empId, dtrDate ORDER BY dtrIn) as nvarchar(100)) as seq
FROM dtrTable
) as t
UNPIVOT (
[Values] FOR [Columns] IN ([dtrIn],[dtrOut])
) as unpvt
) as d
PIVOT (
MAX([Values]) FOR [Columns] IN ([dtrIn1],[dtrOut1],[dtrIn2],[dtrOut2],[dtrIn3],[dtrOut3])
) as pvt
Output:
empId empName dtrDate dtrIn1 dtrOut1 dtrIn2 dtrOut2 dtrIn3 dtrOut3
1 emp1 2016-10-20 2016-10-20 10:00:00.000 2016-10-20 15:00:00.000 2016-10-20 15:30:00.000 2016-10-20 17:00:00.000 2016-10-20 18:30:00.000 2016-10-20 19:00:00.000
2 emp2 2016-10-20 2016-10-20 10:00:00.000 2016-10-20 19:00:00.000 2016-10-20 21:00:00.000 2016-10-20 22:00:00.000 NULL NULL
2 emp2 2016-10-21 2016-10-20 11:00:00.000 2016-10-20 21:00:00.000 NULL NULL NULL NULL
Unpivot part will give you this table:
empId empName dtrDate Columns Values
1 emp1 2016-10-20 dtrIn1 2016-10-20 10:00:00.000
1 emp1 2016-10-20 dtrOut1 2016-10-20 15:00:00.000
1 emp1 2016-10-20 dtrIn2 2016-10-20 15:30:00.000
1 emp1 2016-10-20 dtrOut2 2016-10-20 17:00:00.000
1 emp1 2016-10-20 dtrIn3 2016-10-20 18:30:00.000
1 emp1 2016-10-20 dtrOut3 2016-10-20 19:00:00.000
2 emp2 2016-10-20 dtrIn1 2016-10-20 10:00:00.000
2 emp2 2016-10-20 dtrOut1 2016-10-20 19:00:00.000
2 emp2 2016-10-20 dtrIn2 2016-10-20 21:00:00.000
2 emp2 2016-10-20 dtrOut2 2016-10-20 22:00:00.000
2 emp2 2016-10-21 dtrIn1 2016-10-20 11:00:00.000
2 emp2 2016-10-21 dtrOut1 2016-10-20 21:00:00.000
Here I use ROW_NUMBER() with partitioning to give some order to ins and outs. And also numbers, generated by ROW_NUMBER(), helps to create columns, we cannot use same column names in pivot part.
SQL Server 2008 - Pivot Table
You can easily get the result by implementing 2 windowing functions, ntile() and row_number().
NTILE() will be used to partition your data into "buckets", so when you use NTILE(5) you are going to create 5 buckets for your ClassCodes for each StudentId.
select StudentId, ClassCode,
newCol =
'ClassCode' +
cast(ntile(5) over(partition by StudentId
order by ClassCode) as varchar(1))
from TestClass;
See SQL Fiddle with Demo. This will get your data into the format:
| STUDENTID | CLASSCODE | NEWCOL |
|-----------|-----------|------------|
| 10002 | CHR100 | ClassCode1 |
| 10002 | COM140 | ClassCode1 |
| 10002 | HUM200 | ClassCode2 |
| 10002 | MTH100 | ClassCode3 |
| 10002 | PHY200 | ClassCode4 |
| 10002 | PHY200-L | ClassCode5 |
As you can see the data is now in 5 buckets, these buckets are your new column names ClassCode1, ClassCode2, etc. You will also notice that there are two rows with the ClassCode1, if you apply the PIVOT function now you will only return one row. In order to return multiple rows you will need to apply row_number() to the data.
The row_number() will create a unique sequence for each row of data:
;with cte as
(
select StudentId, ClassCode,
newCol =
'ClassCode' +
cast(ntile(5) over(partition by StudentId
order by ClassCode) as varchar(1))
from TestClass
),
mr as
(
select StudentId, ClassCode,
newCol,
row_number() over(partition by StudentId, newCol order by newCol) seq
from cte
)
select *
from mr;
See SQL Fiddle with Demo. This gets a result of:
| STUDENTID | CLASSCODE | NEWCOL | SEQ |
|-----------|-----------|------------|-----|
| 10002 | CHR100 | ClassCode1 | 1 |
| 10002 | COM140 | ClassCode1 | 2 |
| 10002 | HUM200 | ClassCode2 | 1 |
| 10002 | MTH100 | ClassCode3 | 1 |
| 10002 | PHY200 | ClassCode4 | 1 |
| 10002 | PHY200-L | ClassCode5 | 1 |
The NewCol values that were identical ClassCode1 now have a different sequence number. This will be needed when grouping by your data during the pivot process.
Finally, you can apply the PIVOT function to get the final result:
;with cte as
(
select StudentId, ClassCode,
newCol =
'ClassCode' +
cast(ntile(5) over(partition by StudentId
order by ClassCode) as varchar(1))
from TestClass
),
mr as
(
select StudentId, ClassCode,
newCol,
row_number() over(partition by StudentId, newCol order by newCol) seq
from cte
)
select studentid,
ClassCode1, ClassCode2, ClassCode3,
ClassCode4, ClassCode5
from mr
pivot
(
max(ClassCode)
for NewCol in (ClassCode1, ClassCode2, ClassCode3,
ClassCode4, ClassCode5)
) piv
order by StudentId;
See SQL Fiddle with Demo.
If you wanted to use an aggregate function with a CASE expression like you had in your question then you would still use NTILE() and row_number() but the final code would be:
;with cte as
(
select StudentId, ClassCode,
newCol =
'ClassCode' +
cast(ntile(5) over(partition by StudentId
order by ClassCode) as varchar(1))
from TestClass
),
mr as
(
select StudentId, ClassCode,
newCol,
row_number() over(partition by StudentId, newCol order by newCol) seq
from cte
)
select studentid,
max(case when newcol = 'ClassCode1' then ClassCode end) ClassCode1,
max(case when newcol = 'ClassCode2' then ClassCode end) ClassCode2,
max(case when newcol = 'ClassCode3' then ClassCode end) ClassCode3,
max(case when newcol = 'ClassCode4' then ClassCode end) ClassCode4,
max(case when newcol = 'ClassCode5' then ClassCode end) ClassCode5
from mr
group by StudentId, seq
order by StudentId;
See SQL Fiddle with Demo. Both versions will give a final result of:
| STUDENTID | CLASSCODE1 | CLASSCODE2 | CLASSCODE3 | CLASSCODE4 | CLASSCODE5 |
|-----------|------------|------------|------------|------------|------------|
| 10001 | BIO101 | ENG240 | HUM300 | MTH100 | (null) |
| 10002 | CHR100 | HUM200 | MTH100 | PHY200 | PHY200-L |
| 10002 | COM140 | (null) | (null) | (null) | (null) |
| 10003 | ENG200 | HUM100 | PHY101 | (null) | (null) |
Since you are going to only have 5 columns, there shouldn't be a need to use dynamic SQL to get the result.
Related Topics
Oracle Update Query Using Join
How to Find Third or Nᵗʰ Maximum Salary from Salary Table
How to See the Values of a Table Variable at Debug Time in T-Sql
How to Import .SQL Files into SQLite 3
Autoincrement in Oracle to Already Created Table
Execute Dynamic Query with Go in SQL
How to Compare Two SQLite Databases on Linux
Rodbc Temporary Table Issue When Connecting to Ms SQL Server
How to Turn on Regexp in SQLite3 and Rails 3.1
SQL Server (Tsql) - How to Exec Statements in Parallel
What Is the Mysterious 'Timestamp' Datatype in Sybase
Move SQL Data from One Table to Another
Mysql: Group_Concat with Left Join
Why Using a Udf in a SQL Query Leads to Cartesian Product
Sqlite: Current_Timestamp Is in Gmt, Not the Timezone of the MAChine
Merge Row Values into a CSV (A.K.A Group_Concat for SQL Server)