crosstab query..i think? - 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 2000 Forums

SQL Server Development (2000)

crosstab query..i think?

Author

Topic

joemama
Posting Yak Master

113 Posts

Posted - 2007-08-16 : 20:08:03

i want to make this query

SELECT ImpactedID, WMMID
FROM dbo.WMM_IMPACTED_GROUPS
WHERE (WMMID = 47)

which shows this result

3 47
4 47
2 47
6 47

show this result

47 3 4 2 6

can anyone help?

hey001us
Posting Yak Master

185 Posts

Posted - 2007-08-16 : 22:51:13

SELECT (SELECT ImpactedID FROM dbo.WMM_IMPACTED_GROUPS WHERE WMMID = g.WMMID) ImpactedID ,
(SELECT WMMID FROM dbo.WMM_IMPACTED_GROUPS WHERE WMMID = g.WMMID) WMMID
FROM dbo.WMM_IMPACTED_GROUPS g WHERE (WMMID = 47)

hey

hey001us
Posting Yak Master

185 Posts

Posted - 2007-08-16 : 22:56:20

sorry its wrong the above

hey

ujb
Starting Member

8 Posts

Posted - 2007-08-17 : 00:31:37

SELECT DISTINCT s1.WMMID,
STUFF((SELECT ''+CAST(s2.ImpactedID AS VARCHAR(10))+' ' FROM WMM_IMPACTED_GROUPS AS s2 WHERE s2.WMMID = s1.WMMID ORDER BY ImpactedID FOR XML PATH('')), 1, 0, '') AS ImpactedID
FROM WMM_IMPACTED_GROUPS AS s1
ORDER BY s1.WMMID

In all fairness this isn't my code, its modified from something I learned today off another poster - you know who you are!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-17 : 05:07:45

Great learning, but the XML PATH option is only available for SQL Server 2005, not SQL Server 2000.

There are many concatenation functions here at SQLTeam that can be used for SQL Server 2000. Please feel free to search.
Or check out the FAQ in the New to SQL Server forum.

E 12°55'05.25"
N 56°04'39.16"

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2007-08-17 : 10:21:13

Try below



DECLARE @MT TABLE (
TID	int,
CFLG	int 
)

INSERT	@MT

SELECT 3,47 UNION ALL
SELECT 4,47 UNION ALL
SELECT 2,47 UNION ALL
SELECT 6,47 



select CFLG,
            	
        	max( case when  TID  = '3' then TID else null end) 'colvalue3' 
	        ,max( case when TID = '4' then TID else null end) 'colvalue4' 
		,max( case when TID = '2' then TID else null end) 'colvalue2' 
		,max( case when TID = '6' then TID else null end) 'colvalue6'

FROM
		@MT CD
where CFLG =47 
group by CFLG

ujb
Starting Member

8 Posts

Posted - 2007-08-19 : 02:22:19

Doh, forgot which forum I was in, sorry about that Joe :-)

joemama
Posting Yak Master

113 Posts

Posted - 2007-08-21 : 11:33:42

cool i will give it a try

thanks

quote:
Originally posted by ujb

SELECT DISTINCT s1.WMMID,
STUFF((SELECT ''+CAST(s2.ImpactedID AS VARCHAR(10))+' ' FROM WMM_IMPACTED_GROUPS AS s2 WHERE s2.WMMID = s1.WMMID ORDER BY ImpactedID FOR XML PATH('')), 1, 0, '') AS ImpactedID
FROM WMM_IMPACTED_GROUPS AS s1
ORDER BY s1.WMMID

In all fairness this isn't my code, its modified from something I learned today off another poster - you know who you are!

johnsql
Posting Yak Master

161 Posts

Posted - 2007-10-11 : 11:56:49

quote:
Originally posted by joemama

i want to make this query

SELECT ImpactedID, WMMID
FROM dbo.WMM_IMPACTED_GROUPS
WHERE (WMMID = 47)

which shows this result

3 47
4 47
2 47
6 47

show this result

47 3 4 2 6

can anyone help?

Another try:

DECLARE @t TABLE ( id int,gp int )

INSERT @t

SELECT 3,47 UNION ALL
SELECT 4,47 UNION ALL
SELECT 2,47 UNION ALL
SELECT 6,47

declare @bigString varchar(1000)

select distinct @bigString=gp from @t

select @bigString = @bigString + ' ' + convert(varchar,id) from @t

-- check
select @bigString as [result]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-12 : 02:59:27

As usual, if you use front end application, do concatenation there
or http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources