group by issue - 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)

group by issue

Author

Topic

zion99
Posting Yak Master

141 Posts

Posted - 2008-08-19 : 01:21:39

[code]
DECLARE @TABLE1 TABLE
(
INC INTEGER
)
INSERT INTO @TABLE1
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5

DECLARE @TABLE2 TABLE
(
IDEN int,
INC int,
TYP int,
DESC_COL text
)

INSERT INTO @TABLE2
SELECT 1, 1, 1, 'LOOK' UNION ALL
SELECT 2, 1, 2, 'ABC' UNION ALL
SELECT 3, 1, 3, 'ASDFASDF' UNION ALL
SELECT 4, 1, 4, 'Manual entry will be' UNION ALL
SELECT 5, 1, 4, 'journal posted' UNION ALL
SELECT 6, 1, 5, 'Need to look ' UNION ALL
SELECT 7, 3, 2, 'The' UNION ALL
SELECT 8, 3, 3, 'The batch ' UNION ALL
SELECT 9, 3, 4, 'We have been ' UNION ALL
SELECT 10, 3, 4, 'The DBA team ' UNION ALL
SELECT 11, 3, 5, 'We are working'

;WITH data(INC, TYP, DESC_COL)
AS
(
SELECT t1.INC, t2.TYP,
DESC_COL = STUFF((SELECT DISTINCT TOP 100 PERCENT CONVERT(varchar(MAX), IDEN) + ' ' + CONVERT(varchar(MAX), x.DESC_COL) + '¬¬¬'
FROM @TABLE2 AS x WHERE x.INC = t1.INC AND x.TYP = t2.TYP
ORDER BY CONVERT(varchar(MAX), IDEN) + ' ' + CONVERT(varchar(MAX), x.DESC_COL) + '¬¬¬' desc
FOR XML PATH('')), 1, 1, '')
FROM @TABLE1 t1
LEFT JOIN @TABLE2 t2 ON t1.INC = t2.INC
GROUP BY t1.INC, t2.TYP
)
SELECT INC, [1] AS [TYP-1], [2] AS [TYP-2], [3] AS [TYP-3], [4] AS [TYP-4], [5] AS [TYP-5]
FROM data
pivot
(
MAX(DESC_COL)
FOR TYP IN ([1], [2], [3], [4], [5])
) p

[/code]

this query works fine for sql 2005.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=108216

but now i have to use this query in sql 2000. can anybody please provide me some hint to achieve this.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-19 : 01:49:36

try udf methods given in below link

http://www.projectdmx.com/tsql/rowconcatenate.aspx

zion99
Posting Yak Master

141 Posts

Posted - 2008-08-19 : 02:58:21

[code]
DECLARE @TABLE1 TABLE
(
INC INTEGER
)
INSERT INTO @TABLE1
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5

DECLARE @TABLE2 TABLE
(
IDEN int,
INC int,
TYP int,
DESC_COL text
)

INSERT INTO @TABLE2
SELECT 1, 1, 1, 'LOOK' UNION ALL
SELECT 2, 1, 2, 'ABC' UNION ALL
SELECT 3, 1, 3, 'ASDFASDF' UNION ALL
SELECT 4, 1, 4, 'Manual entry will be' UNION ALL
SELECT 5, 1, 4, 'journal posted' UNION ALL
SELECT 6, 1, 5, 'Need to look ' UNION ALL
SELECT 7, 3, 2, 'The' UNION ALL
SELECT 8, 3, 3, 'The batch ' UNION ALL
SELECT 9, 3, 4, 'We have been ' UNION ALL
SELECT 10, 3, 4, 'The DBA team ' UNION ALL
SELECT 11, 3, 5, 'We are working'

SELECT INC,
MAX( CASE seq WHEN 1 THEN (CONVERT(varchar(1000), DESC_COL)) ELSE '' END ) + ', ' +
MAX( CASE seq WHEN 2 THEN (CONVERT(varchar(1000), DESC_COL)) ELSE '' END ) + ', ' +
MAX( CASE seq WHEN 3 THEN (CONVERT(varchar(1000), DESC_COL)) ELSE '' END ) + ', ' +
MAX( CASE seq WHEN 4 THEN (CONVERT(varchar(1000), DESC_COL)) ELSE '' END ) + ', ' +
MAX( CASE seq WHEN 5 THEN (CONVERT(varchar(1000), DESC_COL)) ELSE '' END )
FROM ( SELECT t1.INC, t1.TYP,t1.DESC_COL,
( SELECT COUNT(*)
FROM @TABLE2 t2
WHERE t2.INC = t1.INC
AND t2.TYP = t1.TYP
AND (CONVERT(varchar(1000), t1.DESC_COL)) <= (CONVERT(varchar(1000), t2.DESC_COL)) )
FROM @TABLE2 t1 ) D
( INC, TYP, seq, DESC_COL )
GROUP BY INC

[/code]
i saw the link, many of the solutions suggested deal with SQL 2005. the query which i have pasted gives the following error:
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-19 : 03:17:26

i think its because DESC_COL is of type text. you cant apply mAX() to text datatype. you need to cast it to varchar if you want to apply max or min.

zion99
Posting Yak Master

141 Posts

Posted - 2008-08-19 : 03:42:31

i have already used convert to cast DESC_COL to varchar.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-19 : 04:00:40

whats the below for?
( INC, TYP, seq, DESC_COL )

zion99
Posting Yak Master

141 Posts

Posted - 2008-08-19 : 04:26:14

[code]
DECLARE @TABLE2 TABLE
(
IDEN int,
INC int,
TYP int,
DESC_COL text
)

[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-19 : 04:32:08

but why are giving it in FROM? is it part of select list?

zion99
Posting Yak Master

141 Posts

Posted - 2008-08-19 : 04:52:59

i want this output


1	 LOOK¬¬¬	 ABC¬¬¬	 ASDFASDF¬¬¬	 journal posted¬¬¬4 Manual entry will be¬¬¬	 Need to look ¬¬¬
2	NULL	NULL	NULL	NULL	NULL
3	NULL	 The¬¬¬	 The batch ¬¬¬	 We have been ¬¬¬10 The DBA team ¬¬¬	1 We are working¬¬¬
4	NULL	NULL	NULL	NULL	NULL
5	NULL	NULL	NULL	NULL	NULL

if i dont include t1.INC, t1.TYP,t1.DESC_COL, i get the following error:
'D' has fewer columns than were specified in the column list.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-19 : 06:17:30

quote:

Originally posted by zion99


DECLARE @TABLE1 TABLE
(
	INC INTEGER
)
INSERT INTO @TABLE1
SELECT	1 UNION ALL
SELECT	2 UNION ALL
SELECT	3 UNION ALL
SELECT	4 UNION ALL
SELECT	5	

DECLARE @TABLE2 TABLE
(
	IDEN		int,
	INC 		int,
	TYP 		int,
	DESC_COL 	text
)

INSERT INTO @TABLE2
SELECT	1,	1,	1,	'LOOK'			UNION ALL
SELECT	2,	1,	2,	'ABC'			UNION ALL
SELECT	3,	1,	3,	'ASDFASDF'		UNION ALL
SELECT	4,	1,	4,	'Manual entry will be'	UNION ALL
SELECT	5,	1,	4,	'journal posted'	UNION ALL
SELECT	6,	1,	5,	'Need to look '		UNION ALL
SELECT	7,	3,	2,	'The'    		UNION ALL
SELECT	8,	3,	3,	'The batch '		UNION ALL
SELECT	9,	3,	4,	'We have been '		UNION ALL
SELECT 10,	3,	4,	'The DBA team '		UNION ALL
SELECT 11,	3,	5,	'We are working' 

SELECT INC,
           MAX( CASE seq WHEN 1 THEN (CONVERT(varchar(1000), DESC_COL)) ELSE '' END ) + ', ' +
           MAX( CASE seq WHEN 2 THEN (CONVERT(varchar(1000), DESC_COL)) ELSE '' END ) + ', ' +
           MAX( CASE seq WHEN 3 THEN (CONVERT(varchar(1000), DESC_COL)) ELSE '' END ) + ', ' +
           MAX( CASE seq WHEN 4 THEN (CONVERT(varchar(1000), DESC_COL)) ELSE '' END ) + ', ' +
           MAX( CASE seq WHEN 5 THEN (CONVERT(varchar(1000), DESC_COL)) ELSE '' END )
           FROM ( SELECT t1.INC, t1.TYP,t1.DESC_COL,
                    ( SELECT COUNT(*) 
                        FROM @TABLE2 t2
                       WHERE t2.INC = t1.INC
                         AND t2.TYP = t1.TYP
			AND (CONVERT(varchar(1000), t1.DESC_COL)) <= (CONVERT(varchar(1000), t2.DESC_COL)) )
               FROM @TABLE2 t1 ) D 
( INC, TYP, DESC_COL,seq )
 GROUP BY INC

i saw the link, many of the solutions suggested deal with SQL 2005. the query which i have pasted gives the following error:
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

Run the changed code

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-19 : 06:20:10

quote:
Originally posted by visakh16

whats the below for?
( INC, TYP, seq, DESC_COL )

They are used as alias names.

See the below example


select col1, col2, col3 from
(
	select 345,'test',7456
) as t 
(col1,col2,col3)

Which is same as


select col1, col2, col3 from
(
	select 345 as col1,'test' as col2,7456 as col3
) as t

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

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

quote:
Originally posted by madhivanan

quote:
Originally posted by visakh16

whats the below for?
( INC, TYP, seq, DESC_COL )

They are used as alias names.

See the below example

select col1, col2, col3 from
(
select 345,'test',7456
) as t
(col1,col2,col3)

Which is same as

select col1, col2, col3 from
(
select 345 as col1,'test' as col2,7456 as col3
) as t

Madhivanan

Failing to plan is Planning to fail

oh...didnt realise they were aliases. thanks for pointing out

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources