Solved

Need queried data in seperate columns in SQL

Posted on 2012-03-23
5
309 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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:kselvia
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 500 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
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.

730 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