Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


T-SQL Query | [ The Training Puzzle ]

The puzzle is very simple. Here you have to present the week’s (wk) column comma separated and group by training and classroom columns. Please post all the possible solutions. Please check out the sample input and expected output for details.

Sample Input

TrainingID Training ClassRoom StartTime Duration Wk
1 SQL Server Silver-Room 10:00 02:00 M
2 SQL Server Silver-Room 10:00 02:00 W
3 SQL Server Silver-Room 10:00 02:00 T
4 SQL Server Silver-Room 10:00 02:00 F
5 ASP.NET Cloud-Room 11:00 01:45 F
6 ASP.NET Cloud-Room 11:00 01:45 M
7 ASP.NET Cloud-Room 11:00 01:45 TH

 

Expected output

 

Training ClassRoom StartTime Duration Weeks
SQL Server Silver-Room 10:00 02:00 M ,W ,T ,F
ASP.NET Cloud-Room 11:00 01:45 F ,M ,TH

Rules/Restrictions

  • The solution should be should use “SELECT” statement or “CTE”.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script

Use the below script to generate the source table and fill them up with the sample data.


--

CREATE TABLE TC26_TrainingInfo 
(
TrainingID INT IDENTITY PRIMARY KEY,
Training VARCHAR(20),
ClassRoom VARCHAR(20),
StartTime CHAR(5),
Duration CHAR(5),
Wk CHAR(2)
)

GO

INSERT INTO TC26_TrainingInfo (
Training, ClassRoom, StartTime, Duration, Wk )
SELECT 'SQL Server','Silver-Room','10:00','02:00','M' UNION ALL
SELECT 'SQL Server','Silver-Room','10:00','02:00','W' UNION ALL
SELECT 'SQL Server','Silver-Room','10:00','02:00','T' UNION ALL
SELECT 'SQL Server','Silver-Room','10:00','02:00','F' UNION ALL
SELECT 'ASP.NET','Cloud-Room','11:00','01:45','F' UNION ALL
SELECT 'ASP.NET','Cloud-Room','11:00','01:45','M' UNION ALL
SELECT 'ASP.NET','Cloud-Room','11:00','01:45','TH'

--

UPDATE – 11-Apr-2015 – Solution 1


--

SELECT Training,ClassRoom, StartTime,Duration,
STUFF(( SELECT ', ' + t1.wk 
FROM TC26_TrainingInfo t1 
WHERE 
	    t.Training = t1.Training 
        AND t.ClassRoom = t1.ClassRoom 
        AND t.StartTime = t1.StartTime 
        AND t.Duration = t1.Duration 
	    FOR XML PATH('')), 1, 1, '') Weeks
FROM TC26_TrainingInfo t
GROUP BY Training,ClassRoom, StartTime,Duration
ORDER BY Training DESC

--

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

http://MSBISkills.com

Advertisements