Tags
Complex SQL Challenges, Complex TSQL Challenge, Interview Qs.SQL SERVER Questions, Interview questions on Joins, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Joins, Joins Interview questions, Joins Puzzle, Learn complex SQL, Learn SQL, Learn T-SQL, PL/SQL Challenges, Puzzles, Queries for SQL Interview, SQL, SQL 2012, SQL 2014, SQL 2014 Interview Questions, SQL Challenge, SQL Challenges, SQL Joins, SQL pl/sql puzzles, SQL Puzzles, SQL Queries, SQL SERVER Interview questions, SQL Skills, SQL Sudoku, SQLSERVER, T SQL Puzzles, T-SQL Challenge, Tough SQL Challenges, Tough SQL Puzzles, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Queries
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
Improving my SQL BI Skills 
SELECT DISTINCT Training, ClassRoom, StartTime, Duration,
STUFF((SELECT distinct ‘,’ + 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(”), TYPE
).value(‘.’, ‘NVARCHAR(MAX)’)
,1,1,”) Weeks
FROM TC26_TrainingInfo T
LikeLike
select Training,ClassRoom,StartTime,Duration,
COALESCE([1], ”) + COALESCE(‘,’ + [2], ”)
+COALESCE(‘,’ + [3], ”) + COALESCE(‘,’ + [4], ”)
+COALESCE([5], ”)
+COALESCE(‘,’ + [6], ”)+COALESCE(‘,’ + [7], ”)
AS weeks
from #TC26_TrainingInfo
pivot(max(wk) for trainingId in([1],[2],[3],[4],[5],[6],[7])) as pp
order by Training desc
LikeLike
select distinct Training, ClassRoom, StartTime, Duration,
(select SUBSTRING(
(select ‘,’+Wk from TC26_TrainingInfo where classroom=t.classroom for XML path (”)), 2, 200000)
) as wk
from TC26_TrainingInfo as t
LikeLike