October 23, 2003 at 10:24 am
Hi, All
I have a Q for U.
I have a table looks like this:
*********************************
ZIPC08 CHAN08 CALL08 BAND08
------ ------ ------ ------
01001 850 WDJC AM
01001 88.5 WJIA FM
01001 88.5 WLJR FM
01001 88.5 WFCH FM
01001 89.3 WALN FM
01001 89.5 WBFR FM
01001 91.9 WMBV FM
01001 1170 WRJL AM
01001 88.7 WELL FM
01001 91.3 WTBJ FM
*********************************
What I want to make is that this way..
I want to pull only 6 stations..
****************************************
ZIP CHAN1 CALL1 BAND1 CHAN2 CALL2 BAND2..
01001 850 WDJC AM 88.5 WJIA FM..
01002
01003
************************************
Any Idea???
Thx in advance.
Jay
October 23, 2003 at 11:37 am
I've hit this several times. Step 1 is to capture the data you have into a
-- -- Create Pivot Table for final output.
-- -- drop table #pivot_columns
CREATE TABLE #pivot_columns (
colidsmallint,
[name]char(30) )
INSERT INTO #pivot_columns (
colid,
[name])
SELECT c.colid,
name = CONVERT(char(30), c.name)
FROM tempdb..sysobjects o
INNER JOIN
tempdb..syscolumns c
ON c.id = o.id
WHERE o.id = OBJECT_ID('tempdb..#RAM_PERF')
ORDER BY c.colid
Where tempdb..#RAM_PERF is the name of your existing data set.
Than you can use this table to dump out the values as you need them. I know it's verbose but this technique always works (for know # of columns). If you are working with variable column widths that's a different problem... We've done them too.
Sample code follows:
--
-- --select * from #pivot_columns
--
SELECTDISTINCT
plant_name =p.plant_desc
, design =d.design_desc
, start_month_ytd = ytd.start_month
, end_month_ytd = ytd.end_month
, start_month_p1y = p1y.start_month
, end_month_p1y = p1y.end_month
, start_month_p2y = p2y.start_month
, end_month_p2y = p2y.end_month
, pc.colid
, report_section =
CASE
WHENpc.colid BETWEEN 5 and 14 THEN 'Sample Summary'
WHENpc.colid BETWEEN 15 and 21 THEN 'Forced Outage Factor (%)'
WHENpc.colid BETWEEN 22 and 28 THEN 'Unscheduled Maintenance Outage Factor (%)'
WHENpc.colid BETWEEN 29 and 35 THEN 'Scheduled Maintenance Outage Factor (%)'
ELSE NULL
END
, name = CASE
WHEN pc.name = 'availability' THEN ' Simple Cycle Plant Availability (%)'
WHEN pc.name = 'reliability' THEN ' Simple Cycle Plant Reliability (%)'
WHEN pc.name = 'sh_per_start' THEN ' Service Hours per Start (Hrs.)'
WHEN pc.name = 'sf' THEN ' Service Factor (%)'
WHEN pc.name = 'average_load' THEN ' Average Load (MW)'
WHEN pc.name = 'unit_cnt' THEN ' Number of Units'
WHEN pc.name = 'fof_gtcs' THEN ' Gas Turbine (Core Systems)'
WHEN pc.name = 'fof_gtca' THEN ' Gas Turbine (Controls & Ancillaries)'
WHEN pc.name = 'fof_gt' THEN ' Gas Turbine'
WHEN pc.name = 'fof_gn' THEN ' Electric Generator - (Gas Turbine)'
WHEN pc.name = 'fof_gtgn' THEN ' Gas Turbine & Electric Generator'
WHEN pc.name = 'fof_gt_se' THEN ' Station Equipment'
WHEN pc.name = 'fof_scp' THEN ' Simple Cycle Plant'
WHEN pc.name = 'usof_gtcs' THEN ' Gas Turbine (Core Systems)'
WHEN pc.name = 'usof_gtca' THEN ' Gas Turbine (Controls & Ancillaries)'
WHEN pc.name = 'usof_gt' THEN ' Gas Turbine'
WHEN pc.name = 'usof_gn' THEN ' Electric Generator - (Gas Turbine)'
WHEN pc.name = 'usof_gtgn' THEN ' Gas Turbine & Electric Generator'
WHEN pc.name = 'usof_gt_se' THEN ' Station Equipment'
WHEN pc.name = 'usof_scp' THEN ' Simple Cycle Plant'
WHEN pc.name = 'sof_gtcs' THEN ' Gas Turbine (Core Systems)'
WHEN pc.name = 'sof_gtca' THEN ' Gas Turbine (Controls & Ancillaries)'
WHEN pc.name = 'sof_gt' THEN ' Gas Turbine'
WHEN pc.name = 'sof_gn' THEN ' Electric Generator - (Gas Turbine)'
WHEN pc.name = 'sof_gtgn' THEN ' Gas Turbine & Electric Generator'
WHEN pc.name = 'sof_gt_se' THEN ' Station Equipment'
WHEN pc.name = 'sof_scp' THEN ' Simple Cycle Plant'
ELSE NULL
END
, value_id_ytd = CASE
WHENpc.colid = 5 THEN ytd.availability
WHENpc.colid = 6 THEN ytd.reliability
WHENpc.colid = 7 THEN ytd.service_hrs
WHENpc.colid = 8 THEN ytd.period_hrs
WHENpc.colid = 9 THEN ytd.sh_per_start
WHENpc.colid = 10 THEN ytd.starts
WHENpc.colid = 11 THEN ytd.sf
WHENpc.colid = 12 THEN ytd.mwh_gener
WHENpc.colid = 13 THEN ytd.average_load
WHENpc.colid = 14 THEN ytd.unit_cnt
WHENpc.colid = 15 THEN ytd.fof_gtcs
WHENpc.colid = 16 THEN ytd.fof_gtca
WHENpc.colid = 17 THEN ytd.fof_gt
WHENpc.colid = 18 THEN ytd.fof_gn
WHENpc.colid = 19 THEN ytd.fof_gtgn
WHENpc.colid = 20 THEN ytd.fof_gt_se
WHENpc.colid = 21 THEN ytd.fof_scp
WHENpc.colid = 22 THEN ytd.usof_gtcs
WHENpc.colid = 23 THEN ytd.usof_gtca
WHENpc.colid = 24 THEN ytd.usof_gt
WHENpc.colid = 25 THEN ytd.usof_gn
WHENpc.colid = 26 THEN ytd.usof_gtgn
WHENpc.colid = 27 THEN ytd.usof_gt_se
WHENpc.colid = 28 THEN ytd.usof_scp
WHENpc.colid = 29 THEN ytd.sof_gtcs
WHENpc.colid = 30 THEN ytd.sof_gtca
WHENpc.colid = 31 THEN ytd.sof_gt
WHENpc.colid = 32 THEN ytd.sof_gn
WHENpc.colid = 33 THEN ytd.sof_gtgn
WHENpc.colid = 34 THEN ytd.sof_gt_se
WHENpc.colid = 35 THEN ytd.sof_scp
ELSENULL
END
, value_id_p1y = CASE
WHENpc.colid = 5 THEN p1y.availability
WHENpc.colid = 6 THEN p1y.reliability
WHENpc.colid = 7 THEN p1y.service_hrs
WHENpc.colid = 8 THEN p1y.period_hrs
WHENpc.colid = 9 THEN p1y.sh_per_start
WHENpc.colid = 10 THEN p1y.starts
WHENpc.colid = 11 THEN p1y.sf
WHENpc.colid = 12 THEN p1y.mwh_gener
WHENpc.colid = 13 THEN p1y.average_load
WHENpc.colid = 14 THEN p1y.unit_cnt
WHENpc.colid = 15 THEN p1y.fof_gtcs
WHENpc.colid = 16 THEN p1y.fof_gtca
WHENpc.colid = 17 THEN p1y.fof_gt
WHENpc.colid = 18 THEN p1y.fof_gn
WHENpc.colid = 19 THEN p1y.fof_gtgn
WHENpc.colid = 20 THEN p1y.fof_gt_se
WHENpc.colid = 21 THEN p1y.fof_scp
WHENpc.colid = 22 THEN p1y.usof_gtcs
WHENpc.colid = 23 THEN p1y.usof_gtca
WHENpc.colid = 24 THEN p1y.usof_gt
WHENpc.colid = 25 THEN p1y.usof_gn
WHENpc.colid = 26 THEN p1y.usof_gtgn
WHENpc.colid = 27 THEN p1y.usof_gt_se
WHENpc.colid = 28 THEN p1y.usof_scp
WHENpc.colid = 29 THEN p1y.sof_gtcs
WHENpc.colid = 30 THEN p1y.sof_gtca
WHENpc.colid = 31 THEN p1y.sof_gt
WHENpc.colid = 32 THEN p1y.sof_gn
WHENpc.colid = 33 THEN p1y.sof_gtgn
WHENpc.colid = 34 THEN p1y.sof_gt_se
WHENpc.colid = 35 THEN p1y.sof_scp
ELSENULL
END
, value_id_p2y = CASE
WHENpc.colid = 5 THEN p2y.availability
WHENpc.colid = 6 THEN p2y.reliability
WHENpc.colid = 7 THEN p2y.service_hrs
WHENpc.colid = 8 THEN p2y.period_hrs
WHENpc.colid = 9 THEN p2y.sh_per_start
WHENpc.colid = 10 THEN p2y.starts
WHENpc.colid = 11 THEN p2y.sf
WHENpc.colid = 12 THEN p2y.mwh_gener
WHENpc.colid = 13 THEN p2y.average_load
WHENpc.colid = 14 THEN p2y.unit_cnt
WHENpc.colid = 15 THEN p2y.fof_gtcs
WHENpc.colid = 16 THEN p2y.fof_gtca
WHENpc.colid = 17 THEN p2y.fof_gt
WHENpc.colid = 18 THEN p2y.fof_gn
WHENpc.colid = 19 THEN p2y.fof_gtgn
WHENpc.colid = 20 THEN p2y.fof_gt_se
WHENpc.colid = 21 THEN p2y.fof_scp
WHENpc.colid = 22 THEN p2y.usof_gtcs
WHENpc.colid = 23 THEN p2y.usof_gtca
WHENpc.colid = 24 THEN p2y.usof_gt
WHENpc.colid = 25 THEN p2y.usof_gn
WHENpc.colid = 26 THEN p2y.usof_gtgn
WHENpc.colid = 27 THEN p2y.usof_gt_se
WHENpc.colid = 28 THEN p2y.usof_scp
WHENpc.colid = 29 THEN p2y.sof_gtcs
WHENpc.colid = 30 THEN p2y.sof_gtca
WHENpc.colid = 31 THEN p2y.sof_gt
WHENpc.colid = 32 THEN p2y.sof_gn
WHENpc.colid = 33 THEN p2y.sof_gtgn
WHENpc.colid = 34 THEN p2y.sof_gt_se
WHENpc.colid = 35 THEN p2y.sof_scp
ELSENULL
END
FROM (SELECT * FROM #ram_perf WHERE time_range = 'YTD') ytd
CROSS JOIN
(SELECT * FROM #ram_perf WHERE time_range = 'P1Y') p1y
CROSS JOIN
(SELECT * FROM #ram_perf WHERE time_range = 'P2Y') p2y
CROSS JOIN
#pivot_columns AS pc
CROSS JOIN
d_design d
CROSS JOIN
d_plant p
WHEREpc.colid >= 5
ANDpc.colid NOT IN (7, 8, 10, 12)
ANDd.design_id = @design_id
ANDp.plant_id = @plant_id
ORDER BY pc.colid ASC
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply