columns to rows

[r***@gmail.com]

Hello,

Below is a table called Projects:

RowID User DateCreated Proj Hours
1 Dan 2013-06-21 100A 6
2 Dan 2013-06-22 100A 3
3 Dan 2013-06-25 300A 1
4 Dan 2013-06-26 300A 7
5 Joe 2013-06-22 500A 5
6 Joe 2013-06-23 500A 5
7 Joe 2013-06-27 600A 10

This query produces the below output:

SELECT [User], right(convert(varchar, DateCreated, 106), 8) as 'Month', [Proj], SUM(HOURS) AS [Hours],
100.0 * SUM(Hours) / SUM(SUM(Hours)) OVER (Partition BY User, right(convert(varchar, DateCreated, 106), 8)) AS [%]
FROM Projects
GROUP BY User, right(convert(varchar, DateCreated, 106), 8), Proj
GO
User Month Proj Hours %
Dan Jun 2012 100A 9 53
Dan Jun 2012 300A 8 47
Joe Jun 2012 500A 10 50
Joe Jun 2012 600A 10 50

How do I make the output look like this?

User Month
Dan Jun 2012 100A 9 53% 300A 8 47%
Joe Jun 2012 500A 10 50% 600A 10 50%

[Erland Sommarskog]

User Month Proj Hours %
Dan Jun 2012 100A 9 53
Dan Jun 2012 300A 8 47
Joe Jun 2012 500A 10 50
Joe Jun 2012 600A 10 50
How do I make the output look like this?
User Month
Dan Jun 2012 100A 9 53% 300A 8 47%
Joe Jun 2012 500A 10 50% 600A 10 50%

That is something you should investigate how to do in your reporting tool.
In SQL this will not be pretty or managable, simply because SQL is designed
to work with normalised tables, and the above output is not particularly
normmalised.

You could do:

WITH CTE AS (
SELECT [User], right(convert(varchar, DateCreated, 106), 8) as 'Month',
[Proj], SUM(HOURS) AS [Hours],
100.0 * SUM(Hours) / SUM(SUM(Hours)) OVER (Partition BY User,
right(convert(varchar, DateCreated, 106), 8)) AS [%],
row_number() OVER(PARTITION BY [User],
right(convert(varchar, DateCreated, 106), 8)
ORDER BY Proj) AS rowno
FROM Projects
GROUP BY User, right(convert(varchar, DateCreated, 106), 8), Proj
)
SELECT [User], Month,
MIN(CASE rowno WHEN 1 THEN Proj END) AS Proj1,
MIN(CASE rowno WHEN 1 THEN Hours END) AS Hours1,
MIN(CASE rowno WHEN 1 THEN [%] END) AS [%1],
MIN(CASE rowno WHEN 2 THEN Proj END) AS Proj2,
MIN(CASE rowno WHEN 2 THEN Hours END) AS Hours2,
MIN(CASE rowno WHEN 2 THEN [%] END) AS [%2]
FROM CTE
GROUP BY [User], Month

But as I said, this is something which is better done in the frontend.

[r***@gmail.com]

Post by Erland Sommarskog

User Month Proj Hours %
Dan Jun 2012 100A 9 53
Dan Jun 2012 300A 8 47
Joe Jun 2012 500A 10 50
Joe Jun 2012 600A 10 50
How do I make the output look like this?
User Month
Dan Jun 2012 100A 9 53% 300A 8 47%
Joe Jun 2012 500A 10 50% 600A 10 50%

That is something you should investigate how to do in your reporting tool.
In SQL this will not be pretty or managable, simply because SQL is designed
to work with normalised tables, and the above output is not particularly
normmalised.
WITH CTE AS (
SELECT [User], right(convert(varchar, DateCreated, 106), 8) as 'Month',
[Proj], SUM(HOURS) AS [Hours],
100.0 * SUM(Hours) / SUM(SUM(Hours)) OVER (Partition BY User,
right(convert(varchar, DateCreated, 106), 8)) AS [%],
row_number() OVER(PARTITION BY [User],
right(convert(varchar, DateCreated, 106), 8)
ORDER BY Proj) AS rowno
FROM Projects
GROUP BY User, right(convert(varchar, DateCreated, 106), 8), Proj
)
SELECT [User], Month,
MIN(CASE rowno WHEN 1 THEN Proj END) AS Proj1,
MIN(CASE rowno WHEN 1 THEN Hours END) AS Hours1,
MIN(CASE rowno WHEN 1 THEN [%] END) AS [%1],
MIN(CASE rowno WHEN 2 THEN Proj END) AS Proj2,
MIN(CASE rowno WHEN 2 THEN Hours END) AS Hours2,
MIN(CASE rowno WHEN 2 THEN [%] END) AS [%2]
FROM CTE
GROUP BY [User], Month
But as I said, this is something which is better done in the frontend.
--
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Thank you very much.

[Babu M]

Post by r***@gmail.com
Hello,
RowID User DateCreated Proj Hours
1 Dan 2013-06-21 100A 6
2 Dan 2013-06-22 100A 3
3 Dan 2013-06-25 300A 1
4 Dan 2013-06-26 300A 7
5 Joe 2013-06-22 500A 5
6 Joe 2013-06-23 500A 5
7 Joe 2013-06-27 600A 10
SELECT [User], right(convert(varchar, DateCreated, 106), 8) as 'Month', [Proj], SUM(HOURS) AS [Hours],
100.0 * SUM(Hours) / SUM(SUM(Hours)) OVER (Partition BY User, right(convert(varchar, DateCreated, 106), 8)) AS [%]
FROM Projects
GROUP BY User, right(convert(varchar, DateCreated, 106), 8), Proj
GO
User Month Proj Hours %
Dan Jun 2012 100A 9 53
Dan Jun 2012 300A 8 47
Joe Jun 2012 500A 10 50
Joe Jun 2012 600A 10 50
How do I make the output look like this?
User Month
Dan Jun 2012 100A 9 53% 300A 8 47%
Joe Jun 2012 500A 10 50% 600A 10 50%