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;
SQL Query Convert columns to rows
You could use UNPIVOT to get the desired result also:
SELECT
*
FROM
(
SELECT
SUM(cases) AS total_cases,
SUM(deaths) AS total_deaths
FROM
myschema.metrics
) UNPIVOT ( value
FOR category
IN ( total_cases,
total_deaths ) );
The output of the above will be:
Category Value
Total_cases 1234
Total_deaths 123
How to convert columns to rows in sql server
Use UNPIVOT. Try something like:
SELECT ID, Page, Line, City, Value
FROM SourceTable
UNPIVOT
(Value FOR City IN
(C01, C02, C03)
)AS unpvt;
Where 'SourceTable' is your source table name. (Note: I can't test this at the moment, so it may not be exactly right.)
Full details here: http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx
Convert columns to one row
You can pivot them
SELECT *
FROM (
SELECT
ITEM
, CONCAT([DAY], RIGHT([No],1)) AS Col
, Qty
FROM YourTable
) Src
PIVOT (
MAX(Qty)
FOR Col IN (
[Monday1], [Monday2],
[Tuesday1], [Tuesday2],
[Wednesday1], [Wednesday2]
)
) Pvt
ORDER BY ITEM;
SQL - Convert column to rows including column name
using cross apply() with values() to unpivot your data in a common table expression, then pivoting it with pivot():
with cte as (
select t.date, v.subject, v.value
from t
cross apply (values ('subj1',subj1),('subj2',subj2),('subj3',subj3),('subj4',subj4)) v(subject,value)
)
select subject, [1],[2],[3]
from cte
pivot (max(value) for [date] in ([1],[2],[3])) p
rextester demo: http://rextester.com/QJMRBF98845
returns:
+---------+----+----+----+
| subject | 1 | 2 | 3 |
+---------+----+----+----+
| subj1 | 20 | 15 | 15 |
| subj2 | 5 | 14 | 14 |
| subj3 | 30 | 29 | 29 |
| subj4 | 7 | 4 | 14 |
+---------+----+----+----+
If you want subject to be called date, then simply alias it in the select:
with cte as (
select t.date, v.subject, v.value
from t
cross apply (values ('subj1',subj1),('subj2',subj2),('subj3',subj3),('subj4',subj4)) v(subject,value)
)
select subject as date, [1],[2],[3]
from cte
pivot (max(value) for [date] in ([1],[2],[3])) p
rextester demo: http://rextester.com/XQAE51432
returns:
+-------+----+----+----+
| date | 1 | 2 | 3 |
+-------+----+----+----+
| subj1 | 20 | 15 | 15 |
| subj2 | 5 | 14 | 14 |
| subj3 | 30 | 29 | 29 |
| subj4 | 7 | 4 | 14 |
+-------+----+----+----+
convert column to row in oracle
This is what PIVOT is designed for:
SELECT *
FROM package
PIVOT (
MAX(offer) AS offer, MAX(revenue) AS revenue
FOR month IN ( 'January' AS january, 'February' AS feburary, 'March' AS march )
)
Which, for the sample data:
CREATE TABLE package (U_id, month, offer,revenue) AS
SELECT 1, 'January', 'offer_1', 45 FROM DUAL UNION ALL
SELECT 1, 'February', 'offer_2', 40 FROM DUAL UNION ALL
SELECT 1, 'March', 'offer_1', 35 FROM DUAL UNION ALL
SELECT 2, 'January', 'offer_2', 40 FROM DUAL UNION ALL
SELECT 2, 'February', 'offer_3', 40 FROM DUAL UNION ALL
SELECT 2, 'March', 'offer_1', 50 FROM DUAL;
Outputs:
U_ID JANUARY_OFFER JANUARY_REVENUE FEBURARY_OFFER FEBURARY_REVENUE MARCH_OFFER MARCH_REVENUE 1 offer_1 45 offer_2 40 offer_1 35 2 offer_2 40 offer_3 40 offer_1 50 convert row values to single column as array (or to multiple columns)
Consider below approach
select empid, totaltax, totaldeductions, taxes, deductions
from emptbl e
left join (
select empid, array_agg(t.tax order by tax_line_item) taxes
from tax t group by empid
) using (empid)
left join (
select empid, array_agg(t.deduction order by ded_line_item) deductions
from deductions t group by empid
) using (empid)if applied to sample data in your question - output is
T-SQL: convert columns to rows and insert/update another table
Here is an option that will dynamically UNPIVOT your data without using Dynamic SQL
To be clear: UNPIVOT would be more performant, but you don't have to enumerate the 50 columns.
This is assuming your columns end with a NUMERIC i.e.
FirstName##Example
Select ID
,FirstName
,LastName
,UniueNumber -- You could use SSN = UniueNumber
From (
SELECT A.ID
,Grp
,Col = replace([Key],Grp,'')
,Value
FROM #data A
Cross Apply (
Select [Key]
,Value
,Grp = substring([Key],patindex('%[0-9]%',[Key]),25)
From OpenJson( (Select A.* For JSON Path,Without_Array_Wrapper ) )
) B
) src
Pivot ( max(Value) for Col in ([FirstName],[LastName],[UniueNumber]) ) pvt
Order By ID,GrpResults
UPDATE XML Version
Select ID
,FirstName
,LastName
,UniueNumber
From (
SELECT A.ID
,Grp = substring(Item,patindex('%[0-9]%',Item),50)
,Col = replace(Item,substring(Item,patindex('%[0-9]%',Item),50),'')
,Value
FROM #data A
Cross Apply ( values (convert(xml,(Select A.* for XML RAW)))) B(XData)
Cross Apply (
Select Item = xAttr.value('local-name(.)', 'varchar(100)')
,Value = xAttr.value('.','varchar(max)')
From B.XData.nodes('//@*') xNode(xAttr)
) C
Where Item not in ('ID')
) src
Pivot ( max(Value) for Col in (FirstName,LastName,UniueNumber) ) pvt
Order By ID,Grp
Related Topics
Insert into Values with Where Clause
Phpmyadmin - Total Record Count Varies
Concatenate Multiple Rows in One Field in Access
Ssis - Performing a Lookup on Another Table to Get Related Column
Reverse- Geocoding: How to Determine the City Closest to a (Lat,Lon) with Bigquery SQL
Parse a Date from Unformatted Text in SQL
SQL Server Query Xml Attribute for an Element Value
What's the Most Efficient Way to Normalize Text from Column into a Table
How to Distinct or Group by a Text (Or Ntext) in SQL Server 2005
How to Change All SQL Columns of One Datatype into Another
Change Separator of Wm_Concat Function of Oracle 11Gr2
Incorrect Parameter Count in the Call to Native Function 'Datediff'
How to Find Duplicate Records in Postgresql
Sparksql Error Table Not Found
How to Import an Excel Spreadsheet into SQL Server 2008R2 Database
