need help with rows into columns

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 2008 Forums

Transact-SQL (2008)

need help with rows into columns

Author

Topic

rudyvbnet
Starting Member

3 Posts

Posted - 2011-04-08 : 12:37:06

Hello all,

I have been struggling with the PIVOT, but can't get it to work. I don't even know if I should be using it.
I'm trying to show as a table or view so I can display the results in a datagrid view.

I need my VStation names in columns, they are now rows.

I need my times still has rows. So I have times on left hand sided vertical, and station names as my columns. And then values as the intersect of OPEN or a name is placed in.

It's to be able to look at schedule quickly to see what stations are open and the ones that are booked for the day.

The number of Vstations will vary, but I have a enabled checked in the table to check if I need it or not.

I supplied some code below, not exactly what I have, but the basics. It's enough to explain what I need to do, and simple enough so it's not confusing. I'm sure I'll have questions after this post.

Thanks for all the help!
Rudy

CREATE TABLE [dbo].[tblTime]
	(Timeid int identity(1,1),
	[Time] [nchar](10),
	[TimeEnabled] [bit])
 

INSERT INTO tblTime
           (Time, TimeEnabled)
SELECT   N'10:30 AM', 1
UNION ALL
SELECT   N'10:35 AM', 1
UNION ALL
SELECT   N'10:40 AM', 1
UNION ALL
SELECT   N'10:45 AM', 1
UNION ALL
SELECT   N'10:50 AM', 1





CREATE TABLE [dbo].[tblVis](
	[VisID] [int] IDENTITY(1,1),
	[VisName] [nvarchar](50))
	
INSERT INTO tblVis	
		(VisName)
SELECT 'VStation1'
UNION ALL
SELECT 'VStation2'
UNION ALL
SELECT 'VStation3'
UNION ALL
SELECT 'VStation4'
UNION ALL
SELECT 'VStation5'
UNION ALL
SELECT 'VStation6'
UNION ALL
SELECT 'VStation7'

CREATE TABLE [dbo].[tblInm](
	[InmID] [int] IDENTITY(1,1),
	[InmName] [nvarchar](50))
	
INSERT INTO tblInm	
		(InmName)
SELECT 'IStation1'
UNION ALL
SELECT 'IStation2'
UNION ALL
SELECT 'IStation3'
UNION ALL
SELECT 'IStation4'
UNION ALL
SELECT 'IStation5'
UNION ALL
SELECT 'IStation6'
UNION ALL
SELECT 'IStation7'


CREATE TABLE tblSchedule(
[SchedID] [int] IDENTITY(1,1),
[VisID] [int],
[InmID] [int],
[SchedDate] [nvarchar](50),
[SchedTime] [nvarchar] (50))

INSERT INTO tblSchedule
		(VisID, InmID, SchedDate, SchedTime)
SELECT 1, 2, '04/08/2011', '10:35 AM'
UNION ALL
SELECT 6, 3, '04/08/2011', '10:35 AM'
UNION ALL
SELECT 4, 5, '04/10/2011', '10:50 AM'


--THIS IS TO FIND OUT WHAT TIME SLOTS ARE OPEN AND WHAT TIMES SLOTS ARE FILLED
SELECT tblTime.Time, tblVis.VisName, tblInm.InmName, tblSchedule.SchedDate
FROM tblSchedule LEFT OUTER JOIN
			tblInm ON tblSchedule.InmID = tblInm.InmID LEFT OUTER JOIN
			tblVis ON tblSchedule.VisID = tblVis.VisID LEFT OUTER JOIN
			tblTime ON tblSchedule.SchedTime = tblTime.Time
WHERE (tblSchedule.SchedDate = RIGHT(CONVERT(varchar(50), GETDATE(), 101), 12))
UNION ALL
SELECT TIME, 'OPEN', 'OPEN', '01/01/1900' from tblTime WHERE not exists (select 1 from tblSchedule 
where tblSchedule.SchedTime = tblTime.Time AND tblSchedule.SchedDate = RIGHT(CONVERT(varchar(50), GETDATE(), 101), 12))	
AND tblTime.TimeEnabled = 'true'





--DROP TABLE tblInm
--DROP TABLE tblSchedule
--DROP TABLE tblVis
--DROP TABLE tblTime

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-04-08 : 12:38:24

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=159206

If you don't have the passion to help people, you have no passion

rudyvbnet
Starting Member

3 Posts

Posted - 2011-04-08 : 12:46:39

Thanks yosiasz! I just saw that post. I'm going to go throguh that and see if i can it to work!

Thanks!

Rudy

rudyvbnet
Starting Member

3 Posts

Posted - 2011-04-08 : 13:21:19

Ok. I'm completely in the weeds! I can't even get the sample to work that you posted. I ran they syntax, creates all the tables. Ran the SP, created that. But know when I run for the create and insert tables, and the EXEC dynamic_pivot, I get an error "Msg 208, Level 16, State 1, Line 1
Invalid object name 'Employee'."

What am I doing wrong to see this. I haven't even tried this on my table yet.

Thanks!

Rudy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources