13,045,564 members (71,497 online)
Rate this:
See more:
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
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:

Solution 1

Yes its possible with help of PIVOT[^].

Simple Way To Use Pivot In SQL Query[^]

Hope this helps.
Maciej Los 26-Jul-13 4:36am

Good links, a 5!
Rate this:

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```
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)

Top Experts
Last 24hrsThis month
 OriginalGriff 288 Graeme_Grant 145 ppolymorphe 104 Atlapure Ambrish 80 kp564 70
 OriginalGriff 4,713 RickZeeland 1,854 ppolymorphe 1,708 F-ES Sitecore 1,553 Dave Kreskowiak 1,349

Advertise | Privacy | Mobile
Web01 | 2.8.170713.1 | Last Updated 26 Jul 2013