[T-SQL] Help with the query!

---

Help with the query! Posted: 18 Mar 2013 10:20 PM PDT I came across a situation as follow[code="sql"]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')[/code]I want the output as MONDAY PHYSICS CHEMISTRY MATHEMATICSTUESDAY COMPUTERSCIENCE BIOLOGY WEDNESDAY BOTANY PHYSICSI tried using Pivot table, but was not able come up with the above output. Could you guys please help!!!

The FOR XML clause is invalid in views, inline functions, derived tables, and subqueries when they contain a set operator. Posted: 29 Apr 2011 02:15 AM PDT I have the following XML statement, where I want the union of several tables to be presented as XML.I get this error: The FOR XML clause is invalid in views, inline functions, derived tables, and subqueries when they contain a set operator. To work around, wrap the SELECT containing a set operator using derived table syntax and apply FOR XML on top of it.My SQL:select ISNULL((SELECT top 10 au.sex,au.firstname,au.middlename,au.lastname,am.email,c.id AS objectid,c.title AS objecttitle,c.friendlyurl as objecturl,3 as objecttype,am.CreateDate FROM aspnet_users au INNER JOIN aspnet_membership am ON am.userid=au.userid INNER JOIN cameras c ON c.userid=au.userid WHERE c.indexboost=0 UNION ALL SELECT top 10 au.sex,au.firstname,au.middlename,au.lastname,am.email,c.id AS objectid,c.title AS objecttitle,c.friendlyurl as objecturl,1 as objecttype,am.CreateDate FROM aspnet_users au INNER JOIN aspnet_membership am ON am.userid=au.userid INNER JOIN locations c ON c.userid=au.userid WHERE c.indexboost=0 AND c.id NOT IN (SELECT objectid FROM emailssent WHERE category=c.objecttype AND emailid=2) order by am.CreateDate asc FOR XML RAW, ELEMENTS ,ROOT ('user')),0) as recordsHow can I fix it to get it to work?Thanks!

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / T-SQL (SS2K8) To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610