?
Solved

Need queried data in seperate columns in SQL

Posted on 2012-03-23
5
Medium Priority
?
313 Views
Last Modified: 2012-03-26
Ok this is extremely complicated so i'll try to explain.  I have data like this:

ID         Date            Data        Reading Type
1          1-1-2012      15             type 1
1          1-1-2012      674           Type 2
1          1-1-2012      547           Type 3

2          1-1-2012      601           Type 2
2          1-1-2012      523           Type 3

I am needing to get the data into a format like this

ID            Date          Type1       Type 2       Type3
1           1-1-2012       15             674           547
2            1-1-2012     NULL         601           523

Ihave no idea how to exactly do this.  Any help would be appreciated.
0
Comment
Question by:Brickwall
  • 3
5 Comments
 
LVL 18

Expert Comment

by:UnifiedIS
ID: 37759867
SELECT A.ID, A.Date, A.Data AS Type1, B.Data AS Type2, C.Date AS Type3
FROM
(SELECT ID, Date, Data
FROM Table
WHERE ReadingType = 'Type 1') A
LEFT OUTER JOIN
(SELECT ID, Data
FROM Table
WHERE ReadingType = 'Type 2') B
ON B.ID = A.ID
LEFT OUTER JOIN
(SELECT ID, Data
FROM Table
WHERE ReadingType = 'Type 3') C
ON C.ID = A.ID
0
 
LVL 18

Expert Comment

by:UnifiedIS
ID: 37759873
Sorry, missed that you had a null for type 1 in your data
This will handle that for you

SELECT A.ID, A.Date, B.Data AS Type1, C.Data AS Type2, D.Date AS Type3
FROM
(SELECT DISTINCT ID, Date FROM Table) A
LEFT OUTER JOIN
(SELECT ID, Date, Data
FROM Table
WHERE ReadingType = 'Type 1') B
ON B.ID = A.ID
LEFT OUTER JOIN
(SELECT ID, Data
FROM Table
WHERE ReadingType = 'Type 2') C
ON C.ID = A.ID
LEFT OUTER JOIN
(SELECT ID, Data
FROM Table
WHERE ReadingType = 'Type 3') D
ON D.ID = A.ID
0
 
LVL 12

Expert Comment

by:Ken Selvia
ID: 37761172
On the off-change you don't know all of the values present in Reading Type before hand, you can use this to generate a dynamic query that will return the same results.

-- Sample data
create table #t (id int, date datetime, data int, [Reading Type] varchar(30) )
insert #t select 1          ,'1-1-2012'      ,15             ,'type 1'
insert #t select 1          ,'1-1-2012'      ,674           ,'Type 2'
insert #t select 1          ,'1-1-2012'      ,547           ,'Type 3'
insert #t select 2          ,'1-1-2012'      ,601           ,'Type 2'
insert #t select 2          ,'1-1-2012'      ,523           ,'Type 3'

select * from #t

id          date                    data        Reading Type
----------- ----------------------- ----------- -------------
1           2012-01-01 00:00:00.000 15          type 1
1           2012-01-01 00:00:00.000 674         Type 2
1           2012-01-01 00:00:00.000 547         Type 3
2           2012-01-01 00:00:00.000 601         Type 2
2           2012-01-01 00:00:00.000 523         Type 3


-- Generate cross-tab output;

DECLARE @sql varchar(8000)
SELECT @sql = COALESCE(@sql + ',', 'set ansi_warnings off;
select id, date, ') +  replace('[~] = sum(case when [Reading Type]=''~'' then data else null end) '+ char(10) +' ','~',[Reading Type]) 
FROM (select distinct top 100 percent [Reading Type] from #t order by [Reading Type] DESC) x
SET @sql = @sql + ' from #t group by id, date;
set ansi_warnings on'
exec ( @sql )

id          date                    type 1      Type 2      Type 3
----------- ----------------------- ----------- ----------- -----------
1           2012-01-01 00:00:00.000 15          674         547
2           2012-01-01 00:00:00.000 NULL        601         523

FYI, the SQL statement that gets executated above is;

set ansi_warnings off;
select id, date, [type 1] = sum(case when [Reading Type]='type 1' then data else null end) 
 ,[Type 2] = sum(case when [Reading Type]='Type 2' then data else null end) 
 ,[Type 3] = sum(case when [Reading Type]='Type 3' then data else null end) 
  from #t group by id, date;
set ansi_warnings on

Open in new window

0
 

Author Comment

by:Brickwall
ID: 37764305
Thanks Guys This is real close.  The only thing i didn't know which is now causeing problems.  Is there are multiple records for the same day.  So for instance type 1 and type 2  hcan have up to 50 records per day, where as type 3 only has one.  I am needing to return just one record for each day.  Please an help would be appreciated.  Thanks.
0
 
LVL 18

Accepted Solution

by:
UnifiedIS earned 2000 total points
ID: 37766482
For your subqueries, you will need to limit by either a "distinct" or an aggregate (Max, min, etc...)

If the multiple records have the same data, you can change each subquery to

(SELECT DISTINCT ID, Date, Data
FROM Table
WHERE ReadingType = 'Type 1') B

If the records are different, which one do you want it to choose?
This will get the one with the maximum Data:
(SELECT ID, Date, MAX(Data) AS MaxData
FROM Table
WHERE ReadingType = 'Type 1'
GROUP BY ID, Date) B
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question