Displaying data in multiple columns from one

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

General SQL Server Forums

New to SQL Server Programming

Displaying data in multiple columns from one

Author

Topic

sqlbug
Posting Yak Master

201 Posts

Posted - 2010-09-16 : 13:33:27

I have the tables:
1. SAMPLE_DATA (SAMPLE_NUM(PK) numeric(12,0),SAMPLE_LOCATION int,SAMPLE_TYPE char(1),SAMPLE_START_DATE datetime,
SAMPLE_END_DATE datetime,SAMPLE_START_TIME int,SAMPLE_END_TIME int)

2. LOCATION_DATA (DATA_ID(PK) int,DATA_SAMPLE_NUM(FK) numeric(12,0),
DATA_PARAMETER char(8),DATA_METHOD char(3),DATA_UNIT char(2),
DATA_VALUE numeric(8,4))

3. FLAG_LOCATIONDATA_XREF (FLAG_SAMPLE_NUM(PK) numeric(12,0),FLAG_PARAMETER(PK) char(8),FLAG_METHOD(PK) char(3),FLAG_UNIT(PK) char(2),FLAG_FLAG(PK) char(1))

For each sample in Sample_Data, there are corresponding rows in the Location_Data table. There are 24 samples for a day.

I need to develop a query that will output 24 hours of data and flags in separate columns. Like SELECT hour1_value, hour1_flag, hour2_value,
hour2_flag...and so on. I can do it for the DATA, because the type is numeric, but don't know how to do it for the FLAG which is a char. Moreover - the flags are in a different table and we can have multiple flags for a data. So I need to join them together, like


SELECT CASE SAMPLE_START_TIME WHEN '0' THEN 
(SELECT FLAG_FLAG + ',' FROM FLAG_STATIONDATA_XREF F2
WHERE F1.FLAG_SAMPLE_NUM = F2.FLAG_SAMPLE_NUM
AND F1.FLAG_PARAMETER = F2.FLAG_PARAMETER
AND F1.FLAG_METHOD = F2.FLAG_METHOD
AND F1.FLAG_UNIT = F2.FLAG_UNIT
ORDER BY FLAG_FLAG FOR XML PATH('')) 
END AS Hour1_Flag,
CASE SAMPLE_START_TIME WHEN '100' THEN 
(SELECT FLAG_FLAG + ',' FROM FLAG_STATIONDATA_XREF F2
WHERE F1.FLAG_SAMPLE_NUM = F2.FLAG_SAMPLE_NUM
AND F1.FLAG_PARAMETER = F2.FLAG_PARAMETER
AND F1.FLAG_METHOD = F2.FLAG_METHOD
AND F1.FLAG_UNIT = F2.FLAG_UNIT
ORDER BY FLAG_FLAG FOR XML PATH('')) END AS Hour2_Flag
FROM LOCATION_DATA STN
	INNER JOIN SAMPLE_DATA SMPD
	ON STN.DATA_SAMPLE_NUM = SMPD.SAMPLE_NUM

	LEFT OUTER JOIN FLAG_STATIONDATA_XREF F1
	ON STN.DATA_SAMPLE_NUM = F1.FLAG_SAMPLE_NUM
	AND STN.DATA_PARAMETER = F1.FLAG_PARAMETER
	AND STN.DATA_METHOD = F1.FLAG_METHOD
	AND STN.DATA_UNIT = F1.FLAG_UNIT

	WHERE SMPD.SAMPLE_STATION = 1158 
	AND SMPD.SAMPLE_TYPE = '1'
	AND SMPD.SAMPLE_START_DATE >= '1-Jan-2008'
	AND SMPD.SAMPLE_END_DATE <= '31-Dec-2009'

But obviously, it will take a long processing time for 24 hours of the
day (only 2 hours are displayed in the query).
For the data part, I am using like:


SELECT Param_Abbrev, Unit_Abbrev, Sample_Start_date, Sample_LOCATION, STN_Name, Method_Name,
SUM(Hour1_value) AS Hour1_value,SUM(Hour2_value) AS Hour2_value,...........SUM(Hour24_value) As Hour24_value
FROM 
(SELECT DISTINCT Param_Abbrev, Unit_Abbrev, Sample_Start_date, Sample_LOCATION, STN_Name, Method_Name,
(CASE SAMPLE_START_TIME WHEN 0 THEN DATA_VALUE END) AS Hour1_value,
(CASE SAMPLE_START_TIME WHEN 100 THEN DATA_VALUE END) AS Hour2_value,
.........
(CASE SAMPLE_START_TIME WHEN 2300 THEN DATA_VALUE END) As Hour24_value
FROM .........

How can I achieve this? Thanks.

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-16 : 14:09:34

OK...can you tell us WHY you want to do that?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

sqlbug
Posting Yak Master

201 Posts

Posted - 2010-09-16 : 14:45:00

To display on a report. We have a new DBMS where table structures are different.
The report is already there. If we cannot do the query this way, we'll have to redo the reports which will be very time consuming. Thanks.

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-16 : 14:49:12

ok...good answer...

Can you supply us the DDL of the tables, some sample data in DML format, and the expected results...

You'll probably get an answer in 5 minutes

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

sqlbug
Posting Yak Master

201 Posts

Posted - 2010-09-16 : 16:09:42

Hi Brett,
Here are the tables:
1. SAMPLE_DATA (SAMPLE_NUM(PK) numeric(12,0),SAMPLE_LOCATION(FK) int,SAMPLE_TYPE char(1),SAMPLE_START_DATE datetime,
SAMPLE_END_DATE datetime,SAMPLE_START_TIME int,SAMPLE_END_TIME int)

INSERT INTO SAMPLE_DATA VALUES(123456,234,'1','1-Jan-2003','1-Jan-2003',0,59)
INSERT INTO SAMPLE_DATA VALUES(123457,234,'1','1-Jan-2003','1-Jan-2003',100,159)

2. LOCATION_DATA (DATA_ID(PK) int,DATA_SAMPLE_NUM(FK) numeric(12,0),
DATA_PARAMETER char(8),DATA_METHOD char(3),DATA_UNIT char(2),
DATA_VALUE numeric(8,4))

INSERT INTO LOCATION_DATA VALUES (10000001, 123456, '42401','092','11',0.0297)
INSERT INTO LOCATION_DATA VALUES (10000001, 123456, '81101','093','07',0.0538)
INSERT INTO LOCATION_DATA VALUES (10000001, 123457, '42401','092','11',0.0314)
INSERT INTO LOCATION_DATA VALUES (10000001, 123457, '81101','093','07',0.0581)

3. FLAG_LOCATIONDATA_XREF (FLAG_SAMPLE_NUM(PK) numeric(12,0),FLAG_PARAMETER(PK) char(8),FLAG_METHOD(PK) char(3),FLAG_UNIT(PK) char(2),FLAG_FLAG(PK) char(1))

INSERT INTO FLAG_LOCATIONDATA_XREF VALUES (123456,'42401','092','11','X')
INSERT INTO FLAG_LOCATIONDATA_XREF VALUES (123456,'42401','092','11','F')
INSERT INTO FLAG_LOCATIONDATA_XREF VALUES (123457,'81101','093','07','C')

4. LOCATION_INFO (STN_ID(PK) int, STN_NAME varchar(100), STN_ABBREV varchar(30))

INSERT INTO LOCATION_INFO VALUES (234, 'Ponoka Rural Station','Ponoka Rural')

5. PARAMETER_INFO (PARAM_ID(PK) char(8),PARAM_NAME varchar(50),PARAM_ABBREV varchar(30))

INSERT INTO PARAMETER_INFO VALUES ('42401','Barometric Pressure','BP')
INSERT INTO PARAMETER_INFO VALUES ('81101','Coefficient of Haze','COH')

6. METHOD_CODES (METHOD_CODE(PK) char(3), METHOD_NAME varchar(100), METHOD_ABBREV varchar(50))

INSERT INTO METHOD_CODES VALUES ('092','Continuous gas Chromatograph','Continuous Gas')
INSERT INTO METHOD_CODES VALUES ('093','Light Scatter (Grimm)','Grimm')

7. UNIT_CODES (UNIT_CODE(PK) char(2),UNIT_NAME varchar(50),UNIT_ABBREV varchar(50))

INSERT INTO UNIT_CODES VALUES ('11','Meters per Second at 10 Meters','m/s')
INSERT INTO UNIT_CODES VALUES ('07','parts per million (by volume)','ppm')

Expected Data:
Param_Abbrev Unit_Abbrev Sample_Start_Date Sample_Location Stn_Name Method_Name Hour1_Value
------------ ----------- ----------------- -------------- -------- ----------- -----------
Hour1_Flag Hour2_Value Hour2_Flag.......Hour24_Value Hour24_Flag
---------- ----------- ---------- ------------ -----------
Barometric Pressure ppm 1-Jan-2003 234 Ponoka Rural Station Light Scatter (Grimm) 0.0538

XF 0.0581 C ..................... 0.0297 NULL

So, basically the problem is to get the 24 hours data for everyday in Data/Flag/Data/Flag/Data/Flag.... format.

Thanks.

sqlbug
Posting Yak Master

201 Posts

Posted - 2010-09-17 : 11:41:16

Hi Brett,
I did not expect answer in 5 minutes - I know its a complex problem. But I hope it wasn't a joke.
Thanks.

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-17 : 11:42:26

no joke...it was the end of my day..figured someone else would pick up..let me have a look

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-17 : 11:45:31

I also have to do work sometime...like now

Do you know how to script the DDL for tables using Management Studio?

Click on the database, Right Click, Tasks, Generate Scripts

The pick these tables to help us out

The DML is fine I believe

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

sqlbug
Posting Yak Master

201 Posts

Posted - 2010-09-17 : 13:00:22

Yes I do. I just thought it's too much text to post and the info I provided should be okay. Anyway - Here we go:

1. CREATE TABLE [dbo].[SAMPLE_DATA](
[SAMPLE_NUM] [numeric](12, 0) IDENTITY(1,1) NOT NULL,
[SAMPLE_TYPE] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SAMPLE_LOCATION] [int] NOT NULL,
[SAMPLE_START_DATE] [datetime] NOT NULL,
[SAMPLE_START_TIME] [int] NOT NULL,
[SAMPLE_END_DATE] [datetime] NOT NULL,
[SAMPLE_END_TIME] [int] NOT NULL,
CONSTRAINT [PK_SAMPLE_DATA] PRIMARY KEY CLUSTERED
(
[SAMPLE_NUM] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

2. CREATE TABLE [dbo].[LOCATION_DATA](
[DATA_ID] [int] IDENTITY(1,1) NOT NULL,
[DATA_SAMPLE_NUM] [numeric](12, 0) NOT NULL,
[DATA_PARAMETER] [char](8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DATA_METHOD] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DATA_UNIT] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DATA_VALUE] [numeric](8, 4) NULL,
CONSTRAINT [PK_DATA_ID] PRIMARY KEY CLUSTERED
(
[DATA_ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

3. CREATE TABLE [dbo].[FLAG_LOCATIONDATA_XREF](
[FLAG_SAMPLE_NUM] [numeric](12, 0) NOT NULL,
[FLAG_PARAMETER] [char](8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[FLAG_METHOD] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[FLAG_UNIT] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[FLAG_FLAG] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_FLAG_STATIONDATA_XREF_1] PRIMARY KEY CLUSTERED
(
[FLAG_SAMPLE_NUM] ASC,
[FLAG_PARAMETER] ASC,
[FLAG_METHOD] ASC,
[FLAG_UNIT] ASC,
[FLAG_FLAG] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

4. CREATE TABLE [dbo].[LOCATION_INFO](
[STN_ID] [int] IDENTITY(1000,1) NOT NULL,
[STN_NAME] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[STN_ABBREV] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_STNPLOT_INFO] PRIMARY KEY CLUSTERED
(
[STN_ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

5. CREATE TABLE [dbo].[PARAMETER_INFO](
[PARAM_ID] [char](8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[PARAM_ABBREV] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PARAM_NAME] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_PARAMETER_INFO] PRIMARY KEY CLUSTERED
(
[PARAM_ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

6. CREATE TABLE [dbo].[METHOD_CODES](
[METHOD_CODE] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[METHOD_ABBREV] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[METHOD_NAME] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_METHOD_CODES] PRIMARY KEY CLUSTERED
(
[METHOD_CODE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

7. CREATE TABLE [dbo].[UNIT_CODES](
[UNIT_CODE] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[UNIT_NAME] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[UNIT_ABBREV] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_UNIT_CODES] PRIMARY KEY CLUSTERED
(
[UNIT_CODE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

sqlbug
Posting Yak Master

201 Posts

Posted - 2010-09-20 : 15:49:32

too busy Brett?

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-20 : 15:59:34

well actually...yes...AND I got a stupid flat tire today....

In any case

Going back to your flags...if you have more than 1...which one do you want? The MIN, the MAX, the last one entered (either by an IDENTITY or datetiem column?)

I have this done dynamically....

http://weblogs.sqlteam.com/brettk/archive/2005/02/23/4171.aspx

But a CROSSTAB might work

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

sqlbug
Posting Yak Master

201 Posts

Posted - 2010-09-22 : 14:11:14

I want all of them added together in a string. Thats why I was using
(SELECT FLAG_FLAG + ',' FROM FLAG_STATIONDATA_XREF F2
WHERE F1.FLAG_SAMPLE_NUM = F2.FLAG_SAMPLE_NUM
AND F1.FLAG_PARAMETER = F2.FLAG_PARAMETER
AND F1.FLAG_METHOD = F2.FLAG_METHOD
AND F1.FLAG_UNIT = F2.FLAG_UNIT
ORDER BY FLAG_FLAG FOR XML PATH('')) END AS Hour2_Flag

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources