March 19, 2013 at 5:20 am
I came across a situation as follow
CREATE TABLE DAYOFWEEK(WeekID INT IDENTITY,DAY NVARCHAR(50))
CREATE TABLE SubjectToStudy(WeekID INT,SUBJECT NVARCHAR(50))
INSERT INTO DAYOFWEEK VALUES('MONDAY')
INSERT INTO DAYOFWEEK VALUES('TUESDAY')
INSERT INTO DAYOFWEEK VALUES('WEDNESDAY')
INSERT INTO SubjectToStudy VALUES(1,'PHYSICS')
INSERT INTO SubjectToStudy VALUES(1,'CHEMISTRY')
INSERT INTO SubjectToStudy VALUES(1,'MATHEMATICS')
INSERT INTO SubjectToStudy VALUES(2,'COMPUTERSCIENCE')
INSERT INTO SubjectToStudy VALUES(2,'BIOLOGY')
INSERT INTO SubjectToStudy VALUES(3,'BOTANY')
INSERT INTO SubjectToStudy VALUES(3,'PHYSICS')
I want the output as
MONDAYPHYSICSCHEMISTRYMATHEMATICS
TUESDAYCOMPUTERSCIENCEBIOLOGY
WEDNESDAYBOTANYPHYSICS
I tried using Pivot table, but was not able come up with the above output. Could you guys please help!!!
March 19, 2013 at 6:13 am
What should your output recordset look like?
Two columns for DAYOFWEEK and SUBJECTS
Or multiple columns like: DAYOFWEEK, SUBJECT1, SUBJECT2, SUBJECT3, ...
If the second is right, is there known maximum number of subjects per any day?
March 19, 2013 at 9:13 am
Here's one possibility.
Select
dw.day + ' ' +
(
Select sts.Subject + ' '
From SubjectToStudy sts
Where dw.WeekID = sts.WeekID
Order by WeekID
For XML Path ('')
) as list
From
DAYOFWEEK dw
;
March 19, 2013 at 9:53 am
I worked on this for awhile and this was the best I could come up with...
-- SAMPLE DATA
IF OBJECT_ID('tempdb..#Weekday') IS NOT NULL DROP TABLE #Weekday; CREATE TABLE #Weekday(Weekday_id int identity primary key, [day] nvarchar(50));
IF OBJECT_ID('tempdb..#SubjectToStudy') IS NOT NULL DROP TABLE #SubjectToStudy; CREATE TABLE #SubjectToStudy(Weekday_id int,[Subject] nvarchar(50));
IF OBJECT_ID('tempdb..#output') IS NOT NULL DROP TABLE #output; CREATE TABLE #output(Weekday_id int, [day] nvarchar(50), classes nvarchar(255));
INSERT INTO #Weekday VALUES(N'MONDAY'),(N'TUESDAY'),(N'WEDNESDAY');
INSERT INTO #SubjectToStudy VALUES(1,N'PHYSICS'),(1,N'CHEMISTRY'),(1,N'MATHEMATICS'),(2,N'COMPUTERSCIENCE'),(2,N'BIOLOGY'),(3,N'BOTANY'),(3,N'PHYSICS')
INSERT INTO #output
SELECT w.Weekday_id, [day], s.[Subject]
FROM #Weekday w
JOIN #SubjectToStudy s ON w.Weekday_id=s.Weekday_id
ORDER BY Weekday_id
--THE ROUTINE:
DECLARE @classlist nvarchar(300)='';
UPDATE #output
SET @classlist=classes=(CASE WHEN @classlist='' THEN [day]+' ' ELSE @classlist+' ' END)+classes
FROM #output
WHERE Weekday_id=1
SET @classlist='';
UPDATE #output
SET @classlist=classes=(CASE WHEN @classlist='' THEN [day]+' ' ELSE @classlist+' ' END)+classes
FROM #output
WHERE Weekday_id=2
SET @classlist='';
UPDATE #output
SET @classlist=classes=(CASE WHEN @classlist='' THEN [day]+' ' ELSE @classlist+' ' END)+classes
FROM #output
WHERE Weekday_id=3;
;WITH answer AS
(SELECT m=DENSE_RANK() OVER (PARTITION BY Weekday_id ORDER BY LEN(classes) DESC),
classes
FROM #output)
SELECT classes FROM answer
WHERE m=1;
--CLEANUP
DROP TABLE #Weekday;
DROP TABLE #SubjectToStudy;
DROP TABLE #output
GO
Nice work k125
-- Itzik Ben-Gan 2001
March 19, 2013 at 10:21 am
Are you sure that OP wants one column with all subjects concatenated?
If not, then he might want dynamic cross-tab:
SELECT dow.[DAY], s.[Subject], ROW_NUMBER() OVER (PARTITION BY dow.[DAY] ORDER BY s.[Subject]) SN
INTO #ds
FROM [DAYOFWEEK] AS dow
JOIN SubjectToStudy AS s
ON s.WeekId = dow.WeekId
DECLARE @sql NVARCHAR(2000) = '';
SELECT @sql = @sql + '
,MAX(CASE WHEN SN = ' + CAST(SN AS VARCHAR) + ' THEN [Subject] ELSE '''' END) AS Subject_' + CAST(SN AS VARCHAR)
FROM (SELECT DISTINCT SN FROM #ds) q ORDER BY SN
SET @SQL = 'SELECT [Day] ' + @SQL + ' FROM #ds GROUP BY [Day] ORDER BY [Day] '
EXEC (@sql)
March 19, 2013 at 10:25 am
Eugene Elutin (3/19/2013)
Are you sure that OP wants one column with all subjects concatenated?
Agreed. 🙂 My query just presents the results in the format the OP listed at the end of the post. But your question to the OP hasn't yet been answered so it's not clear if that's what is really needed.
March 19, 2013 at 10:41 am
kl25 (3/19/2013)
Eugene Elutin (3/19/2013)
Are you sure that OP wants one column with all subjects concatenated?Agreed. 🙂 My query just presents the results in the format the OP listed at the end of the post. But your question to the OP hasn't yet been answered so it's not clear if that's what is really needed.
If you click "Quote" button on OP original post, you will find that is "output" list is tabular.
So, looks like more cross-tab...
March 19, 2013 at 10:47 am
Eugene Elutin (3/19/2013)
kl25 (3/19/2013)
Eugene Elutin (3/19/2013)
Are you sure that OP wants one column with all subjects concatenated?Agreed. 🙂 My query just presents the results in the format the OP listed at the end of the post. But your question to the OP hasn't yet been answered so it's not clear if that's what is really needed.
If you click "Quote" button on OP original post, you will find that is "output" list is tabular.
So, looks like more cross-tab...
Ohh.. :ermm: Thanks. Just learned something about how the forum posts work... I was going off of the display and didn't look at the formatting underneath. Given that, my query probably isn't helpful for this result. Appreciate the info about clicking "Quote".
March 19, 2013 at 1:50 pm
I must admit the guy who wrote the query qith the XML logic was an eye opener for me, I reallyed liked that method best. The way I have been doing it can be veiwed below. njoy
SELECT DAY,+ [1]+ ' ' +[2]+' '+[3]
FROM(
SELECT
DAY,
MAX(CASE
WHEN ROWID = 1 THEN SUBJECT ELSE ''
END) [1],
MAX(CASE
WHEN ROWID = 2 THEN SUBJECT ELSE ''
END)[2],
MAX(CASE
WHEN ROWID = 3 THEN SUBJECT ELSE ''
END)[3]
FROM(
SELECT ROWID= ROW_NUMBER()OVER(PARTITION BY DAY,DW.WEEKID ORDER BY dw.WEEKID), DW.DAY,SS.SUBJECT
FROM #DAYOFWEEK DW
JOIN #SubjectToStudy SS ON SS.WeekID = DW.WeekID
)OQ
GROUP BY DAY
)II
March 19, 2013 at 5:54 pm
raym85 (3/19/2013)
I must admit the guy who wrote the query qith the XML logic was an eye opener for me, I reallyed liked that method best. The way I have been doing it can be veiwed below. njoy
The query using XML is a well known string concatenation method (since SQL2005). It's kind of string aggregate, which concatenates strong values from multiple rows into single column/row.
Your method was a classical fixed columns cross-tab (until you concatenated [1],[2] and [3] columns). It can also be achieved using PIVOT. Dynamic SQL allows to build dynamic cross-tab with unknown number of columns.
All methods are absolute fine and can be used where relevant.
March 20, 2013 at 12:27 am
Thanks Fellas for such brilliant answers. A lot of things to learn from all the answers ... 🙂
March 20, 2013 at 4:28 am
Eugene Elutin (3/19/2013)
If not, then he might want dynamic cross-tab:
SELECT dow.[DAY], s.[Subject], ROW_NUMBER() OVER (PARTITION BY dow.[DAY] ORDER BY s.[Subject]) SN
INTO #ds
FROM [DAYOFWEEK] AS dow
JOIN SubjectToStudy AS s
ON s.WeekId = dow.WeekId
DECLARE @sql NVARCHAR(2000) = '';
SELECT @sql = @sql + '
,MAX(CASE WHEN SN = ' + CAST(SN AS VARCHAR) + ' THEN [Subject] ELSE '''' END) AS Subject_' + CAST(SN AS VARCHAR)
FROM (SELECT DISTINCT SN FROM #ds) q ORDER BY SN
SET @SQL = 'SELECT [Day] ' + @SQL + ' FROM #ds GROUP BY [Day] ORDER BY [Day] '
EXEC (@sql)
Brilliant stuff.. well done:cool:
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply