Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Help with the query! Expand / Collapse
Author
Message
Posted Tuesday, March 19, 2013 5:20 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, October 13, 2014 4:08 AM
Points: 268, Visits: 670
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
MONDAY PHYSICS CHEMISTRY MATHEMATICS
TUESDAY COMPUTERSCIENCE BIOLOGY
WEDNESDAY BOTANY PHYSICS

I tried using Pivot table, but was not able come up with the above output. Could you guys please help!!!
Post #1432575
Posted Tuesday, March 19, 2013 6:13 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:55 AM
Points: 2,873, Visits: 5,185
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?





_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1432593
Posted Tuesday, March 19, 2013 9:13 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, September 18, 2014 1:52 PM
Points: 504, Visits: 1,870
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
;

Post #1432722
Posted Tuesday, March 19, 2013 9:53 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:06 AM
Points: 612, Visits: 2,853
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


-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)

"I can't stress enough the importance of switching from a 'sequential files' mindset to 'set-based' thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. " -- Itzek Ben-Gan 2001

My blog
Post #1432752
Posted Tuesday, March 19, 2013 10:21 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:55 AM
Points: 2,873, Visits: 5,185
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)




_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1432765
Posted Tuesday, March 19, 2013 10:25 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, September 18, 2014 1:52 PM
Points: 504, Visits: 1,870
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.
Post #1432766
Posted Tuesday, March 19, 2013 10:41 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:55 AM
Points: 2,873, Visits: 5,185
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...


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1432773
Posted Tuesday, March 19, 2013 10:47 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, September 18, 2014 1:52 PM
Points: 504, Visits: 1,870
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.. 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".
Post #1432775
Posted Tuesday, March 19, 2013 1:50 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, March 6, 2014 12:39 PM
Points: 376, Visits: 172
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
Post #1432886
Posted Tuesday, March 19, 2013 5:54 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:55 AM
Points: 2,873, Visits: 5,185
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.


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1432947
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse