column into row - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

column into row

Author

Topic

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-01-30 : 07:42:18

I have a proc I'm using to output to Reporting Services. Because of problems at report end I'd like to modify how proc outputs results.

The proc currently has Time column, Occupancy column, AM_PM_Occupancy, and Daily_Occupancy. I would the to pivot AM_PM_Occupancy, and Daily_Occupancy results so in the Time column they have value of 'AM', 'PM' or 'Daily' and their current results placed into Occupancy column.

Here is a example table of how results look now.

CREATE TABLE [dbo].[a](
[ID] [int] IDENTITY(1,1) NOT NULL,
[STime] [varchar](50) NULL,
[RType] [varchar](50) NULL,
[DailyOccupancy] [decimal](18, 0) NULL,
[AM_PM_Occupancy] [decimal](18, 0) NULL,
[Occupancy] [decimal](18, 0) NULL,
CONSTRAINT [PK_a] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Here are some test values:

INSERT INTO a
(STime,Rtype,DailyOccupancy,AM_PM_Occupancy,Occupancy)

Select '10:00','a',10,11,8.2
UNION ALL
Select '10:00','b',15,12,77.2
UNION ALL
Select '10:00','c',22,13,16
UNION ALL
Select '11:00','a',10,11,44.2
UNION ALL
Select '11:00','b',15,12,33.2
UNION ALL
Select '11:00','c',22,13,66
UNION ALL
Select '12:00','a',10,11,81.2
UNION ALL
Select '12:00','b',15,12,65
UNION ALL
Select '12:00','c',22,13,10
UNION ALL
Select '13:00','a',10,15,34.2
UNION ALL
Select '13:00','b',15,16,35.2
UNION ALL
Select '13:00','c',22,17,86
UNION ALL
Select '14:00','a',10,15,33.2
UNION ALL
Select '14:00','b',15,16,30.2
UNION ALL
Select '14:00','c',22,17,81

After pivot there would be 3 new rows for each rType with 'Daily', 'AM' and 'PM' values in the Time column.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-30 : 08:20:45

I am sorry, I have absolutely no idea what you ask for.
Can you post your expected result from the above sample data?

E 12°55'05.63"
N 56°04'39.26"

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-01-30 : 09:17:24

I need to create new rows for AM_PM_Occupancy setting [sTime] value as 'AM' or 'PM' for each [rType] (AM <= 12pm, PM >= 13:00) . The AM_PM_Occupancy result should go into the Occupancy column. And same for [DailyOccupancy] Column - put 'Daily' into [sTime] and the DailyOccupancy value into [Occupancy] column.

Here is an insert to show how new rows would look:

INSERT INTO a
(STime,Rtype,Occupancy)

Select 'Daily','a',10
UNION ALL
Select 'Daily','b',15
UNION ALL
Select 'Daily','c',22
UNION ALL
Select 'AM','a',11
UNION ALL
Select 'AM','b',12
UNION ALL
Select 'AM','c',13
UNION ALL
Select 'PM','a',15
UNION ALL
Select 'PM','b',16
UNION ALL
Select 'PM','c',17

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-30 : 09:37:22

[code]SELECT CASE
WHEN sTime > '12:00' THEN 'PM'
ELSE 'AM'
END AS sTime,
rType,
AM_PM_Occupancy AS Occupancy
FROM a

UNION

SELECT 'Daily' AS sTime,
rType,
DailyOccupancy AS Occupancy
FROM a[/code]But the limit for PM is really >= '12:00'

E 12°55'05.63"
N 56°04'39.26"

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-01-30 : 09:39:17

Maybe I should go back one stage. Visakh had helped me to get the AM, PM and Daily summary columns.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=118654
using:

Select vw.*,Av_DailyOccupancy from (
SELECT [ID],[NumberPresent],[capacity],[Week_day],[AM_PM],
([NumberPresent]/[capacity]) as Occupancy,
AVG([NumberPresent]*1.0/[capacity]) OVER (PARTITION BY Week_day) AS Av_DailyOccupancy,

AVG(CASE WHEN [NumberPresent]*1.0/[capacity]) END) OVER (PARTITION BY [day],[rtype],case
when [SurveyTime] in ('8:00','9:00','10:00','11:00','12:00') then 'AM'
when [SurveyTime] in ('13:00','14:00','15:00','16:00','17:00','18:00','19:00') then 'PM'
end) AS TypeAM_PMOccupancy
FROM dbo].[A]
) as vw

But I now need these summary columns to be displayed as rows with 'AM', 'PM', or 'Daily' in the time column and the calculated value in the Occupancy column. Is it possible to put these calculated values into new rows rather than columns?

I'm not sure if it is easier to put the values into rows at the first stage or to keep the first stage (putting calculated value into new column) then pivot them into new rows.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-30 : 09:43:36

still doesnt make much sense. what's basis for occupancy values? ALso when you you use daily for STime value?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-30 : 09:45:22

seems like what you need is pivot. but from which field you get AM,PM,Daily value?

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-01-30 : 09:56:58

Visakh - in original post I used a CASE of the sTime column to get an AM_PM column - I didn't mention hourly time to try to keep things a little clearer.

I think I will keep your original method to calculate summary and Peso's method to pivot them to rows.

Here is my "final" query (I hope)

Select sTime,rtype,Occupancy
from a
UNION

SELECT CASE
when [STime] in ('8:00','9:00','10:00','11:00','12:00') then 'AM'
when [STime] in ('13:00','14:00','15:00','16:00','17:00','18:00','19:00') then 'PM'
ELSE Null
END AS sTime,
rType,
AM_PM_Occupancy AS Occupancy
FROM a

UNION

SELECT 'Daily' AS sTime,
rType,
DailyOccupancy AS Occupancy
FROM a

Thank you both for all your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources