please see my attached below:
my actual data will be store in table 1, can I create a view that display data like table 2?

- 322
- 1
- 4
- 13
-
Consider using the PIVOT function – Nadeem_MK Sep 27 '13 at 05:25
6 Answers
Yes it is possible and it is called a UNPIVOT
For your case:
SELECT period, value, category
FROM
(SELECT VendorID, charcge,nocharge
FROM Table) p
UNPIVOT
(value FOR category IN
(charge,nocharge)
)AS unpvt;

- 18,261
- 1
- 49
- 69
Query:
DECLARE @temp TABLE
(
period VARCHAR(50)
, charge INT
, no_charge INT
)
INSERT INTO @temp (period, charge, no_charge)
VALUES
('period 1', 100, 300),
('period 2', 200, 400),
('period 3', 300, 200),
('period 4', 500, 200)
SELECT t.*
FROM @temp
CROSS APPLY (
VALUES
(period, charge, 'charge'),
(period, no_charge, 'no_charge')
) t(period, value, category)
Output:
period value category
-------------------- ----------- ---------
period 1 100 charge
period 1 300 no_charge
period 2 200 charge
period 2 400 no_charge
period 3 300 charge
period 3 200 no_charge
period 4 500 charge
period 4 200 no_charge
Execution Plan:
Additional information:

- 119,203
- 23
- 166
- 186
-
2+1 I actually like `cross apply` query much more than `unpivot` (and aggregates more than `pivot`). It looks much more maintainable for me. – Roman Pekar Sep 27 '13 at 05:59
While other answers are good enough, sometimes it's useful to be able not explicitly specify all column names, so you can use xml trick to unpivot rows:
;with cte as (
select t.period, (select t.* for xml raw('row'), type) as Data
from @temp as t
), cte2 as (
select
c.period,
t.c.value('local-name(.)', 'nvarchar(128)') as category,
t.c.value('.', 'nvarchar(max)') as value
from cte as c
outer apply c.Data.nodes('row/@*[local-name() != "period"]') as t(c)
)
select *
from cte2
For really big tables it could perform a bit worse than plain SQL (but I'm using this approach all the time on updated/inserts thousands of rows and it work well for me). OTOH you don't have to modify query when you add new columns to your table. I've tried to consider pros and cons of this approach here - SQL Server : Columns to Rows.

- 1
- 1

- 107,110
- 28
- 195
- 197
Please try using UNPIVOT.
SELECT *
FROM
(SELECT * FROM YourTable) p
UNPIVOT
(Value FOR Category IN
(change, no_change)
)AS unpvt;
The query can be made as a view using the following query:
CREATE VIEW MyView AS
SELECT *
FROM
(SELECT * FROM YourTable) p
UNPIVOT
(Value FOR Category IN
(change, no_change)
)AS unpvt;

- 18,398
- 3
- 51
- 64
Here is a noobish solution coz I am a noob. I don't know whether it stands a chance against answers posted by SQL wizards before in terms of speed.
select period,value,category from (
select case period when 'period 1' then charge
when 'period 2' then charge
when 'period 3'then charge
when 'period 4'then charge
end as value,'charge' as category,
case period when 'period 1' then 'period 1'
when 'period 2' then 'period 2'
when 'period 3' then 'period 3'
when 'period 4' then 'period 4'
end as period
from test
union all
select case period when 'period 1' then nocharge
when 'period 2' then nocharge
when 'period 3'then nocharge
when 'period 4'then nocharge
end as value,'no charge' as category,
case period when 'period 1' then 'period 1'
when 'period 2' then 'period 2'
when 'period 3' then 'period 3'
when 'period 4' then 'period 4'
end as period
from test
)z
order by period

- 28,083
- 20
- 99
- 133