solve this sql query - 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)

solve this sql query

Author

Topic

udaymahajan
Starting Member

17 Posts

Posted - 2008-10-14 : 06:30:46

my sql table as follows

Table name : TestTable

TestID TestNo TestText
1 10 A
2 10 B
3 10 C
4 11 A
5 11 B

i want to display data like as follows

10 A B C
11 A B

for to show output how can i design this query

uday

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-14 : 06:33:46

Are the A B C in same column with spaces as delimeters, or are they three separate columns?

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

udaymahajan
Starting Member

17 Posts

Posted - 2008-10-14 : 06:54:24

yes, the A B C in same column with spaces as delimeters,if you have any solution then plz send

quote:
Originally posted by Peso

Are the A B C in same column with spaces as delimeters, or are they three separate columns?

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

uday

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-14 : 07:34:11

[code]SELECT TestNo,
MAX(CASE WHEN Seq=1 THEN TestText ELSE NULL END),
MAX(CASE WHEN Seq=2 THEN TestText ELSE NULL END),
MAX(CASE WHEN Seq=3 THEN TestText ELSE NULL END)
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY TestNo ORDER BY TestID) AS Seq,*
FROM YourTable)t
GROUP BY TestNo[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-14 : 07:47:14

Same column?

DECLARE	@Sample TABLE
	(
		testID INT,
		testNo INT,
		testText CHAR(1)
	)

INSERT	@Sample
SELECT	1, 10, 'A' UNION ALL
SELECT	2, 10, 'B' UNION ALL
SELECT	3, 10, 'C' UNION ALL
SELECT	4, 11, 'A' UNION ALL
SELECT	5, 11, 'B'

SELECT		n.testNo,
		STUFF(k.i, 1, 1, '') AS theResult
FROM		(
			SELECT		testNo
			FROM		@Sample
			GROUP BY	testNo
		) AS n
CROSS APPLY	(
			SELECT DISTINCT	' ' + y.testText
			FROM		@Sample AS y
			WHERE		n.testNo = y.testNo
			FOR XML		PATH('')
		) AS k(i)

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-14 : 07:56:21

if you want results in single column use Peso's suggestion. i thought question was to cross tab data into diffrenet columns

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources