Crosstab output - 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

Old Forums

CLOSED - General SQL Server

Crosstab output

Author

Topic

kaus
Posting Yak Master

179 Posts

Posted - 2005-08-02 : 18:53:16

Hi - I've got this table

CREATE TABLE [dbo].[Analytical] (
[Analytical_ID] [int] IDENTITY (1, 1) NOT NULL ,
[SiteNo] [int] NULL ,
[LabId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Sample_Date] [datetime] NULL ,
[Analytical_Code] [int] NULL ,
[Result] [float] NULL ,
[FileName] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Units] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DL] [float] NULL ,
[DateEntered] [datetime] NULL ,
[EnteredBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OverRidden] [bit] NULL
) ON [PRIMARY]

the important columns are:
Sample_Date
SiteNo (there are 8 sites as listed below)
Constituent (there are 3 -- TSS, BOD, TDS)
Result (this value goes under SiteX column)

I want to output the data in this format

Sample_Date Constiuent Site1 Site2 Site3 Site4 Site5 Site6 Site7 Site8
20057 TSS 26 21
20057 BOD 25 20
20057 TDS 22 19

I'm using the following query

-- ****Site 1 *******
if object_id('tempdb..#Site1','U') is not null
drop table #Site1
Select
cast(year(Sample_Date) as varchar) + cast(month(sample_date) as varchar) + cast(day(sample_date)as varchar) [Date],
Case Analytical_Code When 1 then 'TSS' When 2 then 'BOD' else 'TDS' end As Constituent,
Result [Site 1]
into #Site1
from Analytical
where
Overridden = 0 and SiteNo = 1
order by Sample_Date, Analytical_Code

-- ****Site 2 *******
if object_id('tempdb..#Site2','U') is not null
drop table #Site2
Select
cast(year(Sample_Date) as varchar) + cast(month(sample_date) as varchar) + cast(day(sample_date)as varchar) [Date],
Case Analytical_Code When 1 then 'TSS' When 2 then 'BOD' else 'TDS' end As Constituent,
Result [Site 2]
into #Site2
from Analytical
where
Overridden = 0 and SiteNo = 2
order by Sample_Date, Analytical_Code
...etc to Site8

Select #Site1.*, #Site2.*, #Site3.*, #Site4.*,#Site5.*, #Site6.*, #Site7.*, #Site8.*
from #Site1

inner join #Site2
on #Site1.[Date] = #Site2.[Date]
and #Site1.Constituent = #Site2.Constituent

inner join #Site3
on #Site1.[Date] = #Site3.[Date]
and #Site1.Constituent = #Site3.Constituent

..etc to site 8

the problem I have is that sometimes all sites dont have data for a sampling_date
or constituent -- and I dont know ahead of time which site to use as the left join

If anyone would have a better way I might approach this I would appreciate it

thank you

Pete

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-03 : 00:55:58

Refer these
http://weblogs.sqlteam.com/jeffs/archive/2005/05.aspx
http://www.mindsdoor.net/SQLTsql/CrossTab.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources