[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Need queried data in seperate columns in SQL

Posted on 2012-03-23
5
Medium Priority
?
312 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 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

656 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