Cross Tab - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Cross Tab

Author

Topic

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2008-12-02 : 06:46:38

Hi,

Ive never seen this done before in sql but how do I cross tab something? For instance:

Student 1, ICT
Student 1, Maths
Student 1, Science

How would I be able in query put all those in the same line, i.e.
Student 1, ICT + Maths + Science.

Is it possible?

Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-02 : 06:53:20

Yes.
See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

E 12°55'05.63"
N 56°04'39.26"

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2008-12-02 : 08:32:30

quote:
Originally posted by Peso

Yes.
See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

E 12°55'05.63"
N 56°04'39.26"

Ive looked at those queries and amended to mine. This works but its just putting everything in one line.

DECLARE @aos varchar(8000)
SELECT @aos = COALESCE(@aos+',', '') + learning_aim_title
FROM
(
SELECT DISTINCT student_id, learning_aim_title
FROM (SELECT student_id, learning_aim_title
FROM (QUERY
) X
ORDER BY student_id

SELECT [List] = substring(@aos,2,len(@aos))

Everything is now just outputted into one line. How can I have multiple lines seperated by every student? Could you advise me into right direction?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-02 : 08:34:19

Your code has nothing to do with my examples in the link.

E 12°55'05.63"
N 56°04'39.26"

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2008-12-02 : 08:36:27

quote:
Originally posted by Peso

Your code has nothing to do with my examples in the link.

E 12°55'05.63"
N 56°04'39.26"

there were several links in the link you provided which lead me to this example code. Plus I didnt see much point using xml if the query is so small.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-02 : 08:38:10

I will post the code in the link again.

DECLARE	@Sample TABLE
	(
		Student VARCHAR(20),
		Class VARCHAR(20)
	)

INSERT	@Sample
SELECT	'Student 1', 'ICT' UNION ALL
SELECT	'Student 1', 'Maths' UNION ALL
SELECT	'Student 1', 'Science' UNION ALL
SELECT	'Student 2', 'Physics' UNION ALL
SELECT	'Student 2', 'Home cooking'

SELECT		s.Student,
		STUFF(t.c, 1, 2, '') AS Classes,
		s.Student + ': ' + STUFF(t.c, 1, 2, '') AS AllTogether
FROM		(
			SELECT		Student
			FROM		@Sample
			GROUP BY	Student
		) AS s
CROSS APPLY	(
			SELECT	', ' + Class
			FROM	@Sample AS e
			WHERE	e.Student = s.Student
			FOR XML	PATH('')
		) AS t(c)

E 12°55'05.63"
N 56°04'39.26"

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2008-12-02 : 08:52:44

But the code below reads from XML doesnt it? Reading from the end of the code. Isnt there a way to do it without reading xml?

quote:

Originally posted by Peso

I will post the code in the link again.

DECLARE	@Sample TABLE
	(
		Student VARCHAR(20),
		Class VARCHAR(20)
	)

INSERT	@Sample
SELECT	'Student 1', 'ICT' UNION ALL
SELECT	'Student 1', 'Maths' UNION ALL
SELECT	'Student 1', 'Science' UNION ALL
SELECT	'Student 2', 'Physics' UNION ALL
SELECT	'Student 2', 'Home cooking'

SELECT		s.Student,
		STUFF(t.c, 1, 2, '') AS Classes,
		s.Student + ': ' + STUFF(t.c, 1, 2, '') AS AllTogether
FROM		(
			SELECT		Student
			FROM		@Sample
			GROUP BY	Student
		) AS s
CROSS APPLY	(
			SELECT	', ' + Class
			FROM	@Sample AS e
			WHERE	e.Student = s.Student
			FOR XML	PATH('')
		) AS t(c)

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-02 : 09:03:05

quote:

Originally posted by cipriani1984

But the code below reads from XML doesnt it? Reading from the end of the code. Isnt there a way to do it without reading xml?

quote:

Originally posted by Peso

I will post the code in the link again.

DECLARE	@Sample TABLE
	(
		Student VARCHAR(20),
		Class VARCHAR(20)
	)

INSERT	@Sample
SELECT	'Student 1', 'ICT' UNION ALL
SELECT	'Student 1', 'Maths' UNION ALL
SELECT	'Student 1', 'Science' UNION ALL
SELECT	'Student 2', 'Physics' UNION ALL
SELECT	'Student 2', 'Home cooking'

SELECT		s.Student,
		STUFF(t.c, 1, 2, '') AS Classes,
		s.Student + ': ' + STUFF(t.c, 1, 2, '') AS AllTogether
FROM		(
			SELECT		Student
			FROM		@Sample
			GROUP BY	Student
		) AS s
CROSS APPLY	(
			SELECT	', ' + Class
			FROM	@Sample AS e
			WHERE	e.Student = s.Student
			FOR XML	PATH('')
		) AS t(c)

E 12°55'05.63"
N 56°04'39.26"

its not reading from xml, its building a dummy xml string for generating the + delimited values and returning it.

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2008-12-02 : 09:28:34

Thanks worked! Thank you guys much appreciated!

quote:
Originally posted by visakh16

quote:
Originally posted by cipriani1984

But the code below reads from XML doesnt it? Reading from the end of the code. Isnt there a way to do it without reading xml?

quote:
Originally posted by Peso

I will post the code in the link again.
DECLARE @Sample TABLE
(
Student VARCHAR(20),
Class VARCHAR(20)
)

INSERT @Sample
SELECT 'Student 1', 'ICT' UNION ALL
SELECT 'Student 1', 'Maths' UNION ALL
SELECT 'Student 1', 'Science' UNION ALL
SELECT 'Student 2', 'Physics' UNION ALL
SELECT 'Student 2', 'Home cooking'

SELECT s.Student,
STUFF(t.c, 1, 2, '') AS Classes,
s.Student + ': ' + STUFF(t.c, 1, 2, '') AS AllTogether
FROM (
SELECT Student
FROM @Sample
GROUP BY Student
) AS s
CROSS APPLY (
SELECT ', ' + Class
FROM @Sample AS e
WHERE e.Student = s.Student
FOR XML PATH('')
) AS t(c)

E 12°55'05.63"
N 56°04'39.26"

its not reading from xml, its building a dummy xml string for generating the + delimited values and returning it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources