Solved

sql 2005 pivot data

Posted on 2013-11-08
5
277 Views
Last Modified: 2013-11-14
id               date                        time
id1             11/8/2013                900
id1             11/8/2013              1000
id2             11/8/2013                910
id2             11/8/2013              1010

if I have data like the above and I want to rearrange it like this:
id               date                        time1          time2
id1             11/8/2013                900           1000
id2             11/8/2013                910           1010

What is the best sql to do that? Acceptable answer must be for sql server 2005 and sql only.
0
Comment
Question by:qube09
5 Comments
 
LVL 32

Expert Comment

by:bhess1
ID: 39634769
There are a lot of assumptions that need to be made to answer this question. And this sort of looks like instructional work, not real world work.  This would not need to be pivot data at all, and as structured really is not suited to be pivot data.  

HINT: Look at using MIN, MAX, and GROUP BY for your query.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 39635113
Are there only ever two times per ID/date, or can there be just one or >2?
0
 

Author Comment

by:qube09
ID: 39635207
generally but could be four
0
 
LVL 41

Accepted Solution

by:
Sharath earned 250 total points
ID: 39635294
try like this.
;WITH CTE AS (SELECT id,[date],[time],
				     ROW_NUMBER() OVER (PARTITION BY id,[date] ORDER BY [time]) rn
				FROM your_table)
SELECT id,[date],
       MAX(CASE rn WHEN 1 THEN [time] END) AS time1,
	   MAX(CASE rn WHEN 2 THEN [time] END) AS time2,
	   MAX(CASE rn WHEN 3 THEN [time] END) AS time3,
	   MAX(CASE rn WHEN 4 THEN [time] END) AS time4
  FROM id,[date]

Open in new window

0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 250 total points
ID: 39635630
nb: line 9 in the comment above should read "FROM CTE" and probably was going on with an extra line for "order by id, [date]"

Another vote for using row_number() as Sharath has, my only additional comment is that you don't have to use the CTE approach, a nested subquery containing the row_number() would work just as well.
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

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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 different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

749 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