Transpose rows into columns in SQL Server 2008 R2
This question is very similar to this one PIVOT rows to columns with more than 1 value returned, where you need to aggregate string data from rows into columns. I will modify that answer to demonstrate how you can convert your data to your final result.
Since you are aggregating string values, you will need to apply either the min() or max() aggregate function, but in order to get the final result to display more than one row you need something to force the multiple rows.
In order to do this you will want to use row_number() to generate a unique sequence number for each parameter in the name. When you apply the PIVOT function, this number will be used in the grouping and you will generate multiple rows:
select Car, Truck, Bicycle
from
(
select vt.name, vp.parameter,
row_number() over(partition by vt.name
order by vt.id) seq
from vehicle_types vt
left join vehicle_parameters vp
on vt.id = vp.vehicletype
) d
pivot
(
max(parameter)
for name in (Car, Truck, Bicycle)
) piv;
See SQL Fiddle with Demo.
This could also be written using an aggregate function with a CASE expression:
select
max(case when name = 'Car' then parameter end) Car,
max(case when name = 'Truck' then parameter end) Truck,
max(case when name = 'Bicycle' then parameter end) Bicycle
from
(
select vt.name, vp.parameter,
row_number() over(partition by vt.name
order by vt.id) seq
from vehicle_types vt
left join vehicle_parameters vp
on vt.id = vp.vehicletype
) d
group by seq;
See SQL Fiddle with Demo.
The above two versions are great if you have a known number of names, then you can hard-code the query but if you don't then you will have to use dynamic SQL:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(name)
from vehicle_types
group by id, name
order by id
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT ' + @cols + '
from
(
select vt.name, vp.parameter,
row_number() over(partition by vt.name
order by vt.id) seq
from vehicle_types vt
left join vehicle_parameters vp
on vt.id = vp.vehicletype
) x
pivot
(
max(parameter)
for name in (' + @cols + ')
) p '
execute sp_executesql @query;
See SQL Fiddle with Demo. All versions give a result:
| CAR | TRUCK | BICYCLE |
|--------|------------------|------------|
| make | maxload | frame |
| year | hasconcretemixer | isroad |
| engine | (null) | ismountain |
sql transpose rows to columns
If you have a limited number of values that you want to convert into columns, then you can easily do this using row_number() with a CASE expression and an aggregate function:
select invoice,
max(case when seq = 1 then item end) item1,
max(case when seq = 1 then description end) description1,
max(case when seq = 1 then qty end) qty1,
max(case when seq = 1 then price end) price1,
max(case when seq = 1 then amount end) amount1,
max(case when seq = 2 then item end) item2,
max(case when seq = 2 then description end) description2,
max(case when seq = 2 then qty end) qty2,
max(case when seq = 2 then price end) price2,
max(case when seq = 2 then amount end) amount2
from
(
select invoice, item, description, qty, price, amount,
row_number() over(partition by invoice order by invoice) seq
from yourtable
) d
group by invoice;
See SQL Fiddle with Demo.
But if you want to use the PIVOT function to get the result, then you will need to look at unpivoting the multiple columns (item, description, qty, price and amount) first, then apply the pivot function. The code using PIVOT will be similar to:
;with cte as
(
select invoice, item, description, qty, price, amount,
row_number() over(partition by invoice order by invoice) seq
from yourtable
)
select invoice,
item1, description1, qty1, price1, amount1,
item2, description2, qty2, price2, amount2
from
(
select invoice,
col = col+cast(seq as varchar(10)),
value
from cte
cross apply
(
select 'item', item union all
select 'description', description union all
select 'qty', cast(qty as varchar(50)) union all
select 'price', cast(price as varchar(50)) union all
select 'amount', cast(amount as varchar(50))
) c (col, value)
) d
pivot
(
max(value)
for col in (item1, description1, qty1, price1, amount1,
item2, description2, qty2, price2, amount2)
) piv;
See SQL Fiddle with Demo. Both will give a result:
| INVOICE | ITEM1 | DESCRIPTION1 | QTY1 | PRICE1 | AMOUNT1 | ITEM2 | DESCRIPTION2 | QTY2 | PRICE2 | AMOUNT2 |
| 1234 | L | labour | 1 | 50 | 50 | P | parts | 2 | 100 | 200 |
| 5555 | P | parts | 2 | 100 | 200 | (null) | (null) | (null) | (null) | (null) |
| 9865 | L | labour | 1 | 25 | 25 | P | parts | 1 | 25 | 25 |
sql convert rows into columns in sql server 2008r2
You didn't provide many details on your table structure but you can use PIVOT to get the final result, but in order to use this you will first want to use row_number() to generate a unique sequence for each hobby per customer:
select customer, Hobby1, Hobby2
from
(
Select c.name customer,
h.name hobby,
'Hobby'+
cast(row_number() over(partition by c.id
order by h.name) as varchar(10)) seq
from dbo.customer c
inner join dbo.hobby h
on h.customerid = c.id
) d
pivot
(
max(hobby)
for seq in (hobby1, Hobby2)
) piv;
See SQL Fiddle with Demo. If you don't want to use the PIVOT function, then you could also use a CASE expression and an aggregate function:
select customer,
max(case when seq = 1 then hobby end) hobby1,
max(case when seq = 2 then hobby end) hobby2
from
(
Select c.name customer,
h.name hobby,
row_number() over(partition by c.id
order by h.name) seq
from dbo.customer c
inner join dbo.hobby h
on h.customerid = c.id
) d
group by customer;
See SQL Fiddle with Demo
Transpose Column to row on SQL Server 2008 R2
Try this
SELECT 'CATAGORY_NAME' COL,CATAGORY_NAME
FROM Yourtable
UNION ALL
SELECT 'GROUP_NAME',GROUP_NAME
FROM Yourtable
UNION ALL
SELECT 'FUNCTION_NAME',FUNCTION_NAME
FROM Yourtable
UNION ALL
SELECT 'CASE_ID',CASE_ID
FROM Yourtable
EDIT :
For your updated question, the following approach can be followed.
SAMPLE TABLE
CREATE TABLE #TEMP(CATAGORY_NAME VARCHAR(200),GROUP_NAME VARCHAR(200), FUNCTION_NAME VARCHAR(200),CASE_ID VARCHAR(200))
INSERT INTO #TEMP
SELECT 'Desbes Optivy', 'TESTING125', 'FAST CASH' , 'G01-TC00101'
UNION ALL
SELECT 'IBM Omron ADM3 (S1G366932)', 'VISA Chip (Single App)', 'FAST CASH', 'G03-TC00501'
UNION ALL
SELECT 'Wincor PC280 (S1A365305)', 'Abnormal SCB Card', 'Abnormal Fast cash', 'G28-TC11804'
UNION ALL
SELECT 'Wincor PC280 (S1A365305)', 'Abnormal VISA Magnetic', 'Abnormal Withdrawal', 'G30-TC15402'
QUERY
;WITH CTE AS
(
-- Here we get a unique id for each row
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT(0))) RNO,*
FROM #TEMP
)
,CTE2 AS
(
-- Hardcode the column names
SELECT 'CATAGORY NAME' COL,CATAGORY_NAME,RNO
FROM CTE
UNION ALL
SELECT 'GROUP NAME',GROUP_NAME,RNO
FROM CTE
UNION ALL
SELECT 'FUNCTION NAME',FUNCTION_NAME,RNO
FROM CTE
UNION ALL
SELECT 'CASE ID',CASE_ID,RNO
FROM CTE
)
-- Select all the data and order by a column according to our logic
-- Row number gets you unique id for each rows
SELECT * FROM CTE2
ORDER BY RNO,
CASE WHEN COL = 'CATAGORY NAME' THEN 1
WHEN COL = 'GROUP NAME' THEN 2
WHEN COL = 'FUNCTION NAME' THEN 3
WHEN COL = 'CASE ID' THEN 4
END
EDIT 2 :
Just try this.
;WITH CTE AS
(
SELECT ROW_NUMBER() OVER(ORDER BY TEST_CASE.CID) RNO,
CATAGORY_NAME,GROUP_NAME,FUNCTION_NAME,
'G'+TEST_CASE.GID + '-TC'+TEST_CASE.CASE_ID CASE_ID,
CASE_NAME,CASE_NAME_TH,EXPECT_RESULT,EXPECT_DETAIL,
INTERFACE,RESULT,TEST_DATE,TEST_BY,REMARK
FROM TEST_CASE
LEFT OUTER JOIN TEST_CATAGORY
ON TEST_CASE.CID = TEST_CATAGORY.CID
AND TEST_CASE.SID = TEST_CATAGORY.SID
LEFT OUTER JOIN TEST_GROUP ON TEST_CASE.GID = TEST_GROUP.GID AND
TEST_CASE.CID = TEST_GROUP.CID WHERE TEST_CASE.SID = 1
)
,CTE2 AS
(
-- Hardcode the column names
SELECT 'CATAGORY NAME' COL,CATAGORY_NAME,RNO
FROM CTE
UNION ALL
SELECT 'GROUP NAME',GROUP_NAME,RNO
FROM CTE
UNION ALL
SELECT 'FUNCTION NAME',FUNCTION_NAME,RNO
FROM CTE
UNION ALL
SELECT 'CASE ID',CASE_ID,RNO
FROM CTE
)
-- Select all the data and order by a column according to our logic
-- Row number gets you unique id for each rows
SELECT * FROM CTE2
ORDER BY RNO,
CASE WHEN COL = 'CATAGORY NAME' THEN 1
WHEN COL = 'GROUP NAME' THEN 2
WHEN COL = 'FUNCTION NAME' THEN 3
WHEN COL = 'CASE ID' THEN 4
END
How to Convert Columns to Rows in Sql Server 2008 R2?
You are pivoting on two columns (department, [check/uncheck]). As far as I know, that means you cannot use SQL Server's pivot syntax.
One way is to "unpivot" (aka "normalize") checked in a subquery. You can then "pivot" (aka "denormalize") the tools column in the outer query:
select department
, [Check/Uncheck]
, sum(case when tools = 'engine' then nr else 0 end) as engine
, sum(case when tools = 'oils' then nr else 0 end) as oils
, sum(case when tools = 'grease' then nr else 0 end) as grease
, sum(case when tools = 'sounds' then nr else 0 end) as sounds
, sum(case when tools = 'wapers' then nr else 0 end) as wapers
from (
select department
, tools
, 'Checked' as [Check/Uncheck]
, checked as nr
from dbo.YourTable
union all
select department
, tools
, 'Unchecked'
, unchecked
from dbo.YourTable
) as SubQueryAlias
group by
Department
, [Check/Uncheck]
order by
Department
, [Check/Uncheck]
Live example at SQL Fiddle.
Related Topics
Least Value But Not Null in Oracle SQL
How to Use Like Condition in SQL with Numeric Field
Why Is My Case Expression Non-Deterministic
Convert Row Data to Column in SQL Server
SQL - Safely Downcast Bigint to Int
What Happens with Duplicates When Inserting Multiple Rows
How to Make a JPA Query with Left Outer Join
Is There a Script to Bypass 50000 Characters for In-Cell Formula
Identifying Transitive Dependencies
How to Back Up a Postgresql Database from Within Psql
How to Return Last Inserted (Auto Incremented) Row Id in Hsql
Accessing Column Alias in Postgresql
Fill Missing Dates in a Date-Sequenced in SQL Using Tally Table
How to Get SQL to Generate Both the Starting and Ending Xml Tags
Syntax Error with Update Query When Join with Some Table