How do i transform rows into columns in sql server 2005
There are similar questions here,here answered in stackoverflow.
You need to use the operator PIVOT in your query to acheive this.Here is the example and explanation on how you can do that.The example is referenced from this source.
---I assumed your tablename as TESTTABLE---
DECLARE @cols NVARCHAR(2000)
DECLARE @query NVARCHAR(4000)
SELECT @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT
'],[' + t.Name
FROM TESTTABLE AS t
ORDER BY '],[' + t.Name
FOR XML PATH('')
), 1, 2, '') + ']'
SET @query = N'SELECT '+ @cols +' FROM
(SELECT t1.Name , t1.Count FROM TESTTABLE AS t1) p
PIVOT (MAX([Count]) FOR Name IN ( '+ @cols +' ))
AS pvt;'
EXECUTE(@query)
Explanation
1.The first part of the query
SELECT @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT
'],[' + t.Name
FROM TESTTABLE AS t
ORDER BY '],[' + t.Name
FOR XML PATH('')
), 1, 2, '') + ']'
gives you a nice flattened result of your Name column values in a single row as follow
[Cheryl],[Drew],[Karen],[Kath],[Kirk],[Matt]
You can learn more about the STUFF and XML PATH here and here.
2.SELECT + @cols + FROM
will select all the rows as coloumn names for the final result set (pvt - step 3)
i.e
Select [Chery],[Drew],[Morgan],[Kath],[Kirk],[Matt]
3.This query pulls all the rows of data that we need to create the cross-tab results. The (p) after the query is creating a temporary table of the results that can then be used to satisfy the query for step 1.
(SELECT t1.Name, t1.Count FROM TESTTABLE AS t1) p
4.The PIVOT expression
PIVOT (MAX (Count) FOR Name IN ( @cols) AS pvt
does the actual summarization and puts the results into a temporary table called pvt as
Chery | Drew | Morgon | Kath | Kirk | Matt
-------------------------------------------
257 1500 13 500 200 76
converting rows into columns in t-sql - sql server 2005
Assuming your data is in aTable:
create FUNCTION toCSV (@id int)
RETURNS varchar(100)
AS
BEGIN
DECLARE @List varchar(100)
SELECT @List = COALESCE(@List + ', ', '') +
CAST(name AS varchar(10))
FROM aTable
WHERE ID = @id
RETURN(@list)
END;
go
Then:
select distinct id, dbo.toCSV(id) from aTable
How to transform Rows into Column Sql Server
Here is a sample query that might help you to achieve your required output. This query will work in case you have that 5 rows in real as well. In case of more rows and variety in data, you need to adjust the query as per your data and required output.
SELECT
MAX(CASE WHEN LabelID = 1 THEN LabelName END) [1],
MAX(CASE WHEN LabelID = 2 THEN LabelName END) [2],
MAX(CASE WHEN LabelID = 3 THEN LabelName END) [3],
MAX(CASE WHEN LabelID = 4 THEN LabelName END) [4],
MAX(CASE WHEN LabelID = 5 THEN LabelName END)[5]
FROM your_table
Simple way to transpose columns and rows in SQL?
There are several ways that you can transform this data. In your original post, you stated that PIVOT
seems too complex for this scenario, but it can be applied very easily using both the UNPIVOT
and PIVOT
functions in SQL Server.
However, if you do not have access to those functions this can be replicated using UNION ALL
to UNPIVOT
and then an aggregate function with a CASE
statement to PIVOT
:
Create Table:
CREATE TABLE yourTable([color] varchar(5), [Paul] int, [John] int, [Tim] int, [Eric] int);
INSERT INTO yourTable
([color], [Paul], [John], [Tim], [Eric])
VALUES
('Red', 1, 5, 1, 3),
('Green', 8, 4, 3, 5),
('Blue', 2, 2, 9, 1);
Union All, Aggregate and CASE Version:
select name,
sum(case when color = 'Red' then value else 0 end) Red,
sum(case when color = 'Green' then value else 0 end) Green,
sum(case when color = 'Blue' then value else 0 end) Blue
from
(
select color, Paul value, 'Paul' name
from yourTable
union all
select color, John value, 'John' name
from yourTable
union all
select color, Tim value, 'Tim' name
from yourTable
union all
select color, Eric value, 'Eric' name
from yourTable
) src
group by name
See SQL Fiddle with Demo
The UNION ALL
performs the UNPIVOT
of the data by transforming the columns Paul, John, Tim, Eric
into separate rows. Then you apply the aggregate function sum()
with the case
statement to get the new columns for each color
.
Unpivot and Pivot Static Version:
Both the UNPIVOT
and PIVOT
functions in SQL server make this transformation much easier. If you know all of the values that you want to transform, you can hard-code them into a static version to get the result:
select name, [Red], [Green], [Blue]
from
(
select color, name, value
from yourtable
unpivot
(
value for name in (Paul, John, Tim, Eric)
) unpiv
) src
pivot
(
sum(value)
for color in ([Red], [Green], [Blue])
) piv
See SQL Fiddle with Demo
The inner query with the UNPIVOT
performs the same function as the UNION ALL
. It takes the list of columns and turns it into rows, the PIVOT
then performs the final transformation into columns.
Dynamic Pivot Version:
If you have an unknown number of columns (Paul, John, Tim, Eric
in your example) and then an unknown number of colors to transform you can use dynamic sql to generate the list to UNPIVOT
and then PIVOT
:
DECLARE @colsUnpivot AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),
@colsPivot as NVARCHAR(MAX)
select @colsUnpivot = stuff((select ','+quotename(C.name)
from sys.columns as C
where C.object_id = object_id('yourtable') and
C.name <> 'color'
for xml path('')), 1, 1, '')
select @colsPivot = STUFF((SELECT ','
+ quotename(color)
from yourtable t
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query
= 'select name, '+@colsPivot+'
from
(
select color, name, value
from yourtable
unpivot
(
value for name in ('+@colsUnpivot+')
) unpiv
) src
pivot
(
sum(value)
for color in ('+@colsPivot+')
) piv'
exec(@query)
See SQL Fiddle with Demo
The dynamic version queries both yourtable
and then the sys.columns
table to generate the list of items to UNPIVOT
and PIVOT
. This is then added to a query string to be executed. The plus of the dynamic version is if you have a changing list of colors
and/or names
this will generate the list at run-time.
All three queries will produce the same result:
| NAME | RED | GREEN | BLUE |
-----------------------------
| Eric | 3 | 5 | 1 |
| John | 5 | 4 | 2 |
| Paul | 1 | 8 | 2 |
| Tim | 1 | 3 | 9 |
SQL Server - Transpose rows into columns
You also need a value field in your query for each id to aggregate on. Then you can do something like this
select [1234], [1235]
from
(
-- replace code below with your query, e.g. select id, value from table
select
id = 1234,
value = 1
union
select
id = 1235,
value = 2
) a
pivot
(
avg(value) for id in ([1234], [1235])
) as pvt
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 |
Convert Database Rows into Columns
If you're using SQL Server 2005 then you can use the Pivot operator.
See this MSDN article.
Here's the SQL Server 2005 T-SQL to do what you want to do:
DECLARE @listCol VARCHAR(2000)
DECLARE @query VARCHAR(4000)
SELECT @listCol = STUFF(( SELECT DISTINCT
'],[' + Hotel
FROM dbo.tblHotels
ORDER BY '],[' + Hotel
FOR XML PATH('')
), 1, 2, '') + ']'
SET @query =
'SELECT * FROM
(SELECT cDate,Hotel,price
FROM dbo.tblHotels) p
PIVOT (SUM(price) FOR Hotel
IN ('+@listCol+')) AS pvt'
EXECUTE (@query)
SQL server: convert rows to columns
You should take a look at PIVOT for switching rows with columns. This prevents a select statement for each month. Something like this:
DECLARE @salesTable TABLE
(
[month] INT,
sales INT
)
-- Note that I use SQL Server 2008 INSERT syntax here for inserting
-- multiple rows in one statement!
INSERT INTO @salesTable
VALUES (0, 2) ,(0, 2) ,(1, 2) ,(1, 2) ,(2, 2)
,(3, 2) ,(3, 2) ,(4, 2) ,(4, 2) ,(5, 2)
,(6, 2) ,(6, 2) ,(7, 2) ,(8, 2) ,(8, 2)
,(9, 2) ,(10, 2) ,(10, 2) ,(11, 2) ,(11, 2)
SELECT [0], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11]
FROM
(
SELECT [month], sales
FROM @salesTable
) AS SourceTable
PIVOT
(
SUM(sales)
FOR [month] IN ([0], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11])
) AS PivotTable
Transpose rows to columns - SQL2005
It can be done using INFORMATION_SCHEMA, UNPIVOT, CAST and EXEC. Here's a working solution.
CREATE TABLE #Data ([Status] int, [Wage] varchar(100), [Tax] decimal(10,3), BigText nvarchar(max))
INSERT #Data VALUES (7, '$12m', 123123.22, 'small island')
SELECT ORDINAL_POSITION, COLUMN_NAME, DATA_TYPE
+ CASE WHEN CHARACTER_MAXIMUM_LENGTH > 0 THEN '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS nvarchar(10)) + ')' WHEN CHARACTER_MAXIMUM_LENGTH = -1 THEN '(max)' ELSE '' END
+ CASE WHEN DATA_TYPE IN ('numeric', 'decimal') AND NUMERIC_PRECISION > 0 THEN '(' + CAST(NUMERIC_PRECISION AS nvarchar(10))
+ CASE WHEN NUMERIC_SCALE > 0 THEN ',' + CAST(NUMERIC_SCALE AS NVARCHAR(10)) ELSE '' END
+ ')' ELSE '' END DATA_TYPE
INTO #Columns
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE C.TABLE_NAME LIKE '#Data%'
ORDER BY C.ORDINAL_POSITION
DECLARE @selectList nvarchar(max), @columnNames nvarchar(max)
SELECT @selectList = ISNULL(@selectList + ',', '') + 'CAST(' + QUOTENAME(COLUMN_NAME) + ' AS nvarchar(max)) ' + QUOTENAME(COLUMN_NAME),
@columnNames = ISNULL(@columnNames + ',', '') + QUOTENAME(COLUMN_NAME)
FROM #Columns
DECLARE @unpivot nvarchar(max)
SET @unpivot = '
SELECT b.Column_Name, b.DataValue, c.Data_Type
FROM
(
SELECT ' + @selectList + ' FROM #Data
) a
UNPIVOT (DataValue FOR Column_Name IN (' + @columnNames + ')) b
JOIN #Columns c ON c.Column_Name = b.Column_Name
'
EXEC (@unpivot)
Transpose rows into columns in SQL without using aggregate functions like MAX, AVG etc
You can use max()
with characters/string. A simple old style pivot
will work for this:
select
RollNo
, Q1 = max(case when QuesBlock = 1 then RespVal else null end)
, Q2 = max(case when QuesBlock = 2 then RespVal else null end)
, Q3 = max(case when QuesBlock = 3 then RespVal else null end)
from MyDataTable
group by RollNo;
or with pivot()
like so:
select
RollNo
, Q1
, Q2
, Q3
from (select RollNo, QuesBlock='Q'+convert(varchar(2),QuesBlock), RespVal
from MyDataTable) as i
pivot (max(RespVal) for QuesBlock in (Q1,Q2,Q3)) as p;
or dynamically pivot()
like so:
declare @query nvarchar(max);
declare @cols nvarchar(max);
select @cols = stuff((select ','+quotename('Q'+convert(varchar(2),QuesBlock))
from MyDataTable as C
group by c.QuesBlock
order by c.QuesBlock
for xml path('')), 1, 1, '');
set @query = 'select RollNo, '+@cols+'
from(select RollNo, QuesBlock=''Q''+convert(varchar(2),QuesBlock), RespVal
from MyDataTable) as i
pivot
(
max(RespVal)
for QuesBlock in ('+@cols+')
) p';
exec sp_executesql @query;
test setup: http://rextester.com/TURW69000
all three return:
+---------+----+----+----+
| RollNo | Q1 | Q2 | Q3 |
+---------+----+----+----+
| mba0001 | A | B | D |
| mba0002 | C | A | B |
| mba0003 | B | C | A |
+---------+----+----+----+
Related Topics
How to Group MySQL Rows with Same Column Value into One Row
Differencebetween Rowsbetween and Rangebetween
I Don't Understand Collation? (Mysql, Rdbms, Character Sets)
How to Insert Null Values into SQL Server
Count Rows Per Hour in SQL Server with Full Date-Time Value as Result
Are There Any Free Tools to Generate 'Insert Into' Scripts in Ms SQL Server
Using Input from a Text File for Where Clause
How to Find All Rows with a Null Value in Any Column Using Postgresql
Subtract One Day from Datetime
What Are the Recommended Learning Material for Ssis
How to Concatenate Text in a Query in SQL Server
How to Export Data with Oracle SQL Developer
A How to Escape %% When Building Like Queries in Rails 3/Activerecord
Exec Stored Procedure into Dynamic Temp Table
SQL Injection After Removing All Single-Quotes and Dash-Characters
Calling a Stored Procedure in Oracle with in and Out Parameters
How to Store Longitude & Latitude as a Geography in SQL Server 2014