Transact SQL Query help

Transact-SQL
1

Hi Team,

I have data in a table as below

Date Course Count Sum
2018-Jan-01 Science 150 1450
2018-Feb-01 Maths 120 1600

Output should be as below
Date Course data
2018-Jan-01 Science Count is 150
2018-Jan-01 Science Sum is 1450
2018-Feb-01 Maths Count is 120
2018-Feb-01 Maths Sum is 1600

Please help on Transact Sql query.

2

OK -- this looks like homework. Please check your notes. then give it a go on your own. then, it still doesn't work, post what you have written so far.

3

Hi Gbritton,

Thanks for your reply.

I got the expected result by using CTE and Union All. But my query is referring main table twice to get Count record and Sum record.

I am trying to re-write the query which reads main table only once to get both records.

4

Would you please post what you have so far?

5

Hi Gbritton,

Here is Tsql.
With CTE
as
(SELECT [Year]
,[Month]
,[Course]
,COUNT(count) count_Data
,SUM (sum) Sum_Data
FROM table_name
GROUP BY
[Course],
[Year],
[Month]
)

SELECT CAST(CAST(Year as varchar(4)) + '/' + CAST(Month as varchar(2)) +  '/' + '01' as datetime) date
      ,[Course] + ' count is' course
	  ,Count_data Data	       
FROM cte
UNION ALL
SELECT CAST(CAST(Year as varchar(4)) + '/' + CAST(Month as varchar(2)) + '/' + '01' as datetime) date
      ,[Course] + 'Sum is' course
	  ,Sum_Data Data	       
FROM cte
6 
SELECT 
    CAST(CAST([Year] * 10000 + [Month] * 100 + 1 AS varchar(8)) AS date) AS date,
    Course,
    Course + ' ' + data + ' is ' + CAST([Count] AS varchar(10)) AS data
FROM dbo.table_name
CROSS APPLY (
    VALUES('Count'),('Sum')
) AS data(data)