Solved

Need queried data in seperate columns in SQL

Posted on 2012-03-23
5
305 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: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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
MS SQL 2014 get SPIDs of users 6 27
how to fix this error 14 48
SQL Date Retrival 7 30
How toselect unique values 3 10
Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now