4

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? enter image description here

Joehom Sum
  • 322
  • 1
  • 4
  • 13

6 Answers6

4

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;
Giannis Paraskevopoulos
  • 18,261
  • 1
  • 49
  • 69
3

Please see PIVOT/UNPIVOT for what you need

Thomas Wood
  • 799
  • 7
  • 12
3

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:

tt

Additional information:

Detecting Potential Bottlenecks with the help of Profiler

Devart
  • 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
3

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.

sql fiddle demo

Community
  • 1
  • 1
Roman Pekar
  • 107,110
  • 28
  • 195
  • 197
2

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;
TechDo
  • 18,398
  • 3
  • 51
  • 64
0

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

SQL FIDDLE

Mudassir Hasan
  • 28,083
  • 20
  • 99
  • 133