Solved

Need queried data in seperate columns in SQL

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone 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

Suggested Solutions

Title # Comments Views Activity
SSIS On fail action 5 38
Solar Winds can't see SQL Server Express 17 32
Where is the Help Section? 8 25
SQL Recursion 6 18
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

856 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