May 4, 2008 at 6:42 pm
Hi,
Trying to solve a reporting issues here, and cant quite get my head around it.
I have a table which stores test results, one row per test.
Now what I need to do with this is generate a table or view, that reports this multiple of test into a column for each test in the series, with the data appropriately
test date table
CREATE TABLE [dbo].[sampleTest](
[sampleTestID] [int] IDENTITY(1,1) NOT NULL,
[sampleID] [int] NOT NULL,
[testID] [int] NOT NULL,
[sampleTestValue] [varchar](50)
)
Sample Data
-------------
11000010001Shower
2100001000238
31000010003Y
41000010004Y
510000100051
610000100064.1
71000010007150
8100001000822
910000100091300
1010000100105.0
CREATE TABLE [dbo].[test](
[testID] [int] NOT NULL,
[testName] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[testNotes] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[testUnits] [varchar](20) COLLATE Latin1_General_CI_AS NOT NULL,
[baseType] [varchar](20) COLLATE Latin1_General_CI_AS NOT NULL,
[testMinValue] [varchar](10) COLLATE Latin1_General_CI_AS NULL,
[testMaxValue] [varchar](10) COLLATE Latin1_General_CI_AS NULL
)
Sample Data
-------------
10001Weather ConditionNULL StringNULLNULL
10002Rainfall NULLmmNumberNULLNULL
10003River Dam 1 Yes/NoNULLNULL
10004Kauri DamNULL Yes/NoNULLNULL
10005Pump On DutyNULLPump NumberNumberNULLNULL
10006Plant FlowNULLl/sNumberNULLNULL
10007Plant FlowNULLm3/hrNumberNULLNULL
10008Plant NULLHrs RunNumberNULLNULL
10009Treated WaterNULLm3/dayNumberNULLNULL
10010PCDR NULLkg/24hrsNumberNULLNULL
What I would like to generate is a table/view that would look like
10001 10002 10003 10004 10005 10006 10007 10008 10009 10010
-------------------------------------------------------------------------------------------
Shower| 38 | Y | Y | 1 | 4.1 | 150 | 22 | 1300 | 5.0
Thanks
May 5, 2008 at 1:37 am
hI PsycloneDBA,
try this one. this method will solve your problem.
SELECT 'Shower' AS [10001],'38' AS [10002],'Y' AS [10003],'Y' AS [10004],'1' AS [10005],4.1 AS [10006],150 AS [10007],22 AS [10008],1300 AS [10009],5.0 AS [10010] FROM
(SELECT SampleTestValue
FROM SampleTest) AS P
PIVOT
(
MAX(SampleTestValue)
FOR SampleTestValue IN([10001],[10002],[10003],[10004],[10005],[10006],[10007],[10008],[10009],[10010])
)
AS Pvt
-----:)
May 5, 2008 at 4:17 pm
Thanks for the heads up.
That answered my question.
As I need a dynamic generation of tables I added some dynamic sql around it.
Posted the final script here incase anyone else found some use..
DECLARE @reportID int
SET @reportID = 1
DECLARE @columns varchar(4000)
DECLARE @columns2 varchar(4000)
SELECT @columns = COALESCE(@columns + ',','')
+ '[' + convert(varchar,test.testID) + '] AS'
+ '['
+ COALESCE(convert(varchar,test.testName) ,'')
+ COALESCE('-' + convert(varchar,test.testNotes),'')
+ COALESCE('(' + convert(varchar,test.testUnits)+ ')' ,'')
+ ']'
FROM sampleTest inner join
sample on sample.sampleID = sampleTest.sampleID inner join
report on report.siteID = sample.siteID inner join
reportDefinition on reportDefinition.reportID = report.reportID inner join
test on test.TestID = sampleTest.TestID
and report.reportID = @reportID
and sampleTest.testID = reportDefinition.testID
SELECT @columns2 = COALESCE(@columns2 + ',','')
+ '[' + convert(varchar,test.testID) + ']'
FROM sampleTest inner join
sample on sample.sampleID = sampleTest.sampleID inner join
report on report.siteID = sample.siteID inner join
reportDefinition on reportDefinition.reportID = report.reportID inner join
test on test.TestID = sampleTest.TestID
and report.reportID = @reportID
and sampleTest.testID = reportDefinition.testID
set @columns = 'SELECT SampleDate, ' + @columns
set @columns = @columns + '
FROM
(SELECT convert(varchar,sampleDateTime,5) as SampleDate, TestID, sampleTestValue
FROM sample inner join
sampleTest on sample.sampleID = sampleTest.sampleID
) p
PIVOT
(MAX(sampleTestValue) for TestID IN
('
set @columns = @columns + @columns2
set @columns = @columns + '
)
) AS pvt
ORDER BY SampleDate'
exec(@columns )
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply