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
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

zion99
Posting Yak Master

141 Posts

Posted - 2008-08-19 : 03:42:31
i have already used convert to cast DESC_COL to varchar.
Go to Top of Page

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 )
Go to Top of Page

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]
Go to Top of Page

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?
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -