T:SQL: select values from rows as columns
It's easy to do this without PIVOT keyword, just by grouping
select
P.ProfileID,
min(case when PD.PropertyName = 'FirstName' then P.PropertyValue else null end) as FirstName,
min(case when PD.PropertyName = 'LastName' then P.PropertyValue else null end) as LastName,
min(case when PD.PropertyName = 'Salary' then P.PropertyValue else null end) as Salary
from Profiles as P
left outer join PropertyDefinitions as PD on PD.PropertyDefinitionID = P.PropertyDefinitionID
group by P.ProfileID
you can also do this with PIVOT keyword
select
*
from
(
select P.ProfileID, P.PropertyValue, PD.PropertyName
from Profiles as P
left outer join PropertyDefinitions as PD on PD.PropertyDefinitionID = P.PropertyDefinitionID
) as P
pivot
(
min(P.PropertyValue)
for P.PropertyName in ([FirstName], [LastName], [Salary])
) as PIV
UPDATE: For dynamic number of properties - take a look at Increment value in SQL SELECT statement
Efficiently convert rows to columns in sql server
There are several ways that you can transform data from multiple rows into columns.
Using PIVOT
In SQL Server you can use the PIVOT function to transform the data from rows to columns:
select Firstname, Amount, PostalCode, LastName, AccountNumber
from
(
select value, columnname
from yourtable
) d
pivot
(
max(value)
for columnname in (Firstname, Amount, PostalCode, LastName, AccountNumber)
) piv;
See Demo.
Pivot with unknown number of columnnames
If you have an unknown number of columnnames that you want to transpose, then you can use dynamic SQL:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(ColumnName)
from yourtable
group by ColumnName, id
order by id
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = N'SELECT ' + @cols + N' from
(
select value, ColumnName
from yourtable
) x
pivot
(
max(value)
for ColumnName in (' + @cols + N')
) p '
exec sp_executesql @query;
See Demo.
Using an aggregate function
If you do not want to use the PIVOT function, then you can use an aggregate function with a CASE expression:
select
max(case when columnname = 'FirstName' then value end) Firstname,
max(case when columnname = 'Amount' then value end) Amount,
max(case when columnname = 'PostalCode' then value end) PostalCode,
max(case when columnname = 'LastName' then value end) LastName,
max(case when columnname = 'AccountNumber' then value end) AccountNumber
from yourtable
See Demo.
Using multiple joins
This could also be completed using multiple joins, but you will need some column to associate each of the rows which you do not have in your sample data. But the basic syntax would be:
select fn.value as FirstName,
a.value as Amount,
pc.value as PostalCode,
ln.value as LastName,
an.value as AccountNumber
from yourtable fn
left join yourtable a
on fn.somecol = a.somecol
and a.columnname = 'Amount'
left join yourtable pc
on fn.somecol = pc.somecol
and pc.columnname = 'PostalCode'
left join yourtable ln
on fn.somecol = ln.somecol
and ln.columnname = 'LastName'
left join yourtable an
on fn.somecol = an.somecol
and an.columnname = 'AccountNumber'
where fn.columnname = 'Firstname'
Query to select data from row into column in SQL Server
You would need to use Pivoting to solve this type of Problem.
Lets create a small demo table T2 and proceed from it.
Create table T2 (type varchar(100),Value varchar(100));
insert into T2 Values('Name','John');
insert into T2 Values('Gender','Male');
insert into T2 Values('Address','New City');
insert into T2 Values('Phone','62813');
Now if this is the exact number then we can hard code column value of type and use it in our sql as shown below.
SELECT [Name]
,[Gender]
,[Address]
,[Phone]
FROM (
SELECT type
,Value
FROM T2
) AS T1
PIVOT(Min(Value) FOR type IN (
[Name]
,[Gender]
,[Address]
,[Phone]
)) PVT;
But if there are lot of rows in your table and you want it to be dynamic then you can use dynamic query to handle such type of problems.
DECLARE @dml AS NVARCHAR(MAX);
DECLARE @ColumnName AS NVARCHAR(MAX);
SELECT @ColumnName = ISNULL(@ColumnName + ',', '') + QUOTENAME(type)
FROM (
SELECT type
FROM T2
) AS T1;
--Prepare the PIVOT query using the dynamic
SET @dml = N'SELECT ' + @ColumnName + ' FROM
(
SELECT type,
Value
FROM T2
) AS T1
PIVOT (Min(Value)
FOR type IN (' + @ColumnName + ')) AS PVT'
--Print @DynamicPivotQuery
--Execute the Dynamic Pivot Query
EXEC sp_executesql @dml
This will give you the desired result.
TSQL turn table rows into columns with column name and value coming from a row
You probably want to try a Dynamic Pivot Query. This will allow you to build the columns based on the type values in your second table.
There are several ways to get the column names into the format you'll need for the pivot query.. this is one way.
DECLARE @columns VARCHAR(MAX)
SELECT @columns = COALESCE(@columns + ',','') + QUOTENAME(type)
FROM (SELECT DISTINCT [type] FROM secondTable) st
ORDER BY st.[type]
This will give a string in the format of [Type1],[Type2],[Type3].. this allows us to use this string in the Select and also the Pivot part of the dynamic query.
Since you're doing a join, you need to create a subquery to pivot on, since there will be multiple ID columns which aren't allowed in pivots.
So build your query using something like this.
DECLARE @sql NVARCHAR(MAX)
SET @sql =
'SELECT * FROM (
SELECT ft.ID,
ft.Col1,
ft.Col2,
ft.Col3,
st.type,
st.value
FROM
firstTable ft
JOIN secondTable st ON ft.ID = st.ID
) t
PIVOT
(
MAX(Value)
FOR type in (' + @columns + ')
) p
'
EXECUTE sp_executesql @sql
You would of course use LEFT JOIN in your subquery if you want all values in the firstTable even if they're not in the secondTable.
Then you can execute this query using EXEC(@sql) or Execute sq_executesql @sql.
SQL Fiddle
SQL Server : Columns to Rows
You can use the UNPIVOT function to convert the columns into rows:
select id, entityId,
indicatorname,
indicatorvalue
from yourtable
unpivot
(
indicatorvalue
for indicatorname in (Indicator1, Indicator2, Indicator3)
) unpiv;
Note, the datatypes of the columns you are unpivoting must be the same so you might have to convert the datatypes prior to applying the unpivot.
You could also use CROSS APPLY with UNION ALL to convert the columns:
select id, entityid,
indicatorname,
indicatorvalue
from yourtable
cross apply
(
select 'Indicator1', Indicator1 union all
select 'Indicator2', Indicator2 union all
select 'Indicator3', Indicator3 union all
select 'Indicator4', Indicator4
) c (indicatorname, indicatorvalue);
Depending on your version of SQL Server you could even use CROSS APPLY with the VALUES clause:
select id, entityid,
indicatorname,
indicatorvalue
from yourtable
cross apply
(
values
('Indicator1', Indicator1),
('Indicator2', Indicator2),
('Indicator3', Indicator3),
('Indicator4', Indicator4)
) c (indicatorname, indicatorvalue);
Finally, if you have 150 columns to unpivot and you don't want to hard-code the entire query, then you could generate the sql statement using dynamic SQL:
DECLARE @colsUnpivot AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @colsUnpivot
= stuff((select ','+quotename(C.column_name)
from information_schema.columns as C
where C.table_name = 'yourtable' and
C.column_name like 'Indicator%'
for xml path('')), 1, 1, '')
set @query
= 'select id, entityId,
indicatorname,
indicatorvalue
from yourtable
unpivot
(
indicatorvalue
for indicatorname in ('+ @colsunpivot +')
) u'
exec sp_executesql @query;
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: 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
T-SQL:Transform variable number of rows into columns
You're trying to pivot your results and include a counter in your column name. Since I'm presuming you don't know the potential number of columns, you'll need to use Dynamic SQL to accomplish this.
This should be close using ROW_NUMBER to get the counter:
declare @cols AS NVARCHAR(MAX),
@colswithalias AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
set @colswithalias = STUFF((SELECT distinct ',' + QUOTENAME(col1)
+ ' AS Col'
+ CAST(ROW_NUMBER() OVER (ORDER BY col1) as varchar(10))
FROM yourtable
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @cols = STUFF((SELECT distinct ',' + QUOTENAME(col1)
FROM yourtable
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT ' + @colswithalias + '
from
(
select col1 rn, col1
from yourtable
) x
pivot
(
max(rn)
for col1 in (' + @cols + ')
) p '
execute(@query)
- SQL Fiddle Demo
Related Topics
Should a Composite Primary Key Be Clustered in SQL Server
SQL Scheduling - Select All Rooms Available for Given Date Range
Sqlite3 (Or General SQL) Retrieve Nth Row of a Query Result
Create Temp Table with Range of Numbers
Retrieve Aggregates for Arbitrary Time Intervals
H2 SQL Database - Insert If the Record Does Not Exist
Best Practices for the Order of Joined Columns in a SQL Join
How to Find Duplicate Entries and Delete the Oldest Ones in SQL
How to Make This SQL More Efficient
How to Change the Primary Key to Be Non-Clustered
Ssrs Report Builder - Only Show Header on First Page (With Page Numbers)
Split/Explode Comma Delimited String with Sybase SQL Anywhere
Native Insert Query in Hibernate + Spring Data
Oracle 10G Express Home Page Is Not Coming Up
How to Calculate Balances in an Accounting Software Using Postgres Window Function
No Fields for Dynamic SQL Stored Procedure in Ssrs with Set Fmtonly
Retrieving Column and Other Metadata Information in Teradata