Click here to Skip to main content
13,045,564 members (71,497 online)
Rate this:
 
Please Sign up or sign in to vote.
my que is

i am getting data in datatable in below form
id     date     hours
1      22/7/13    4
1      23/7/13    5
10     22/7/13    4
10     23/7/13    3
11     24/7/13    8
6      25/7/13    8
6      26/7/13    8


i want this data in below form
id    22/7/13   23/7/13   24/7/13    25/7/13   26/7/13  
1      4          5         -          -        -
10     4          3         -          -        -
11     -          -         8          -        -
6      -          -         -          8        8

can anyone know how to do this...pls help
Posted 25-Jul-13 21:23pm
Kesar08252
Updated 25-Jul-13 21:26pm
Maciej Los255.3K
v2
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Yes its possible with help of PIVOT[^].

Simple Way To Use Pivot In SQL Query[^]

Hope this helps.
  Permalink  
Comments
Maciej Los 26-Jul-13 4:36am
   
Good links, a 5!
Please, see my answer ;)
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

Try this:
SET DATEFORMAT dmy;
 
CREATE TABLE #tmp (id INT, date DATETIME, hours INT)
 
INSERT INTO #tmp (id, date, hours)
SELECT 1, '22/07/2013', 4
UNION ALL SELECT 1, '23/07/2013', 5
UNION ALL SELECT 10, '22/07/2013', 4
UNION ALL SELECT 10, '23/07/2013', 3
UNION ALL SELECT 11, '24/07/2013', 8
UNION ALL SELECT 6, '25/07/2013', 8
UNION ALL SELECT 6, '26/07/2013', 8
 
DECLARE @cols VARCHAR(300)
DECLARE @dt VARCHAR(2000)
DECLARE @pt VARCHAR(4000)
 
SET @cols = STUFF((SELECT DISTINCT '],[' + CONVERT(VARCHAR(10),O.date,121)
					FROM #tmp AS O
					ORDER BY '],[' + CONVERT(VARCHAR(10),O.date,121)
			FOR XML PATH('')),1,2,'') + ']'
 
SET @dt = N'SELECT id, CONVERT(VARCHAR(10),date,121) AS date, hours
		FROM #tmp'
EXEC(@dt)
 
SET @pt = N'SELECT id, ' + @cols + ' ' +
		'FROM(' + @dt + ') AS DT ' +
		'PIVOT(MAX(hours) FOR date IN(' + @cols + ')) AS PT ' +
		'ORDER BY id'
 
EXEC(@pt)
DROP TABLE #tmp
  Permalink  
Comments
Sushil Mate 26-Jul-13 4:41am
   
You complete me, I guess he will go through my links & try to learn about it. he might try & fail but he has your answer to look upon. :P my +5
Maciej Los 26-Jul-13 4:48am
   
Thank you ;)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web01 | 2.8.170713.1 | Last Updated 26 Jul 2013
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100