Author |
Topic |
zion99
Posting Yak Master
141 Posts |
Posted - 2008-08-19 : 01:21:39
|
[code]DECLARE @TABLE1 TABLE( INC INTEGER)INSERT INTO @TABLE1SELECT 1 UNION ALLSELECT 2 UNION ALLSELECT 3 UNION ALLSELECT 4 UNION ALLSELECT 5 DECLARE @TABLE2 TABLE( IDEN int, INC int, TYP int, DESC_COL text)INSERT INTO @TABLE2SELECT 1, 1, 1, 'LOOK' UNION ALLSELECT 2, 1, 2, 'ABC' UNION ALLSELECT 3, 1, 3, 'ASDFASDF' UNION ALLSELECT 4, 1, 4, 'Manual entry will be' UNION ALLSELECT 5, 1, 4, 'journal posted' UNION ALLSELECT 6, 1, 5, 'Need to look ' UNION ALLSELECT 7, 3, 2, 'The' UNION ALLSELECT 8, 3, 3, 'The batch ' UNION ALLSELECT 9, 3, 4, 'We have been ' UNION ALLSELECT 10, 3, 4, 'The DBA team ' UNION ALLSELECT 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=108216but 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 linkhttp://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 @TABLE1SELECT 1 UNION ALLSELECT 2 UNION ALLSELECT 3 UNION ALLSELECT 4 UNION ALLSELECT 5 DECLARE @TABLE2 TABLE( IDEN int, INC int, TYP int, DESC_COL text)INSERT INTO @TABLE2SELECT 1, 1, 1, 'LOOK' UNION ALLSELECT 2, 1, 2, 'ABC' UNION ALLSELECT 3, 1, 3, 'ASDFASDF' UNION ALLSELECT 4, 1, 4, 'Manual entry will be' UNION ALLSELECT 5, 1, 4, 'journal posted' UNION ALLSELECT 6, 1, 5, 'Need to look ' UNION ALLSELECT 7, 3, 2, 'The' UNION ALLSELECT 8, 3, 3, 'The batch ' UNION ALLSELECT 9, 3, 4, 'We have been ' UNION ALLSELECT 10, 3, 4, 'The DBA team ' UNION ALLSELECT 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 output1 LOOK¬¬¬ ABC¬¬¬ ASDFASDF¬¬¬ journal posted¬¬¬4 Manual entry will be¬¬¬ Need to look ¬¬¬2 NULL NULL NULL NULL NULL3 NULL The¬¬¬ The batch ¬¬¬ We have been ¬¬¬10 The DBA team ¬¬¬ 1 We are working¬¬¬4 NULL NULL NULL NULL NULL5 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 @TABLE1SELECT 1 UNION ALLSELECT 2 UNION ALLSELECT 3 UNION ALLSELECT 4 UNION ALLSELECT 5 DECLARE @TABLE2 TABLE( IDEN int, INC int, TYP int, DESC_COL text)INSERT INTO @TABLE2SELECT 1, 1, 1, 'LOOK' UNION ALLSELECT 2, 1, 2, 'ABC' UNION ALLSELECT 3, 1, 3, 'ASDFASDF' UNION ALLSELECT 4, 1, 4, 'Manual entry will be' UNION ALLSELECT 5, 1, 4, 'journal posted' UNION ALLSELECT 6, 1, 5, 'Need to look ' UNION ALLSELECT 7, 3, 2, 'The' UNION ALLSELECT 8, 3, 3, 'The batch ' UNION ALLSELECT 9, 3, 4, 'We have been ' UNION ALLSELECT 10, 3, 4, 'The DBA team ' UNION ALLSELECT 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 codeMadhivananFailing 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 exampleselect col1, col2, col3 from( select 345,'test',7456) as t (col1,col2,col3) Which is same asselect col1, col2, col3 from( select 345 as col1,'test' as col2,7456 as col3) as t MadhivananFailing 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 exampleselect col1, col2, col3 from( select 345,'test',7456) as t (col1,col2,col3) Which is same asselect col1, col2, col3 from( select 345 as col1,'test' as col2,7456 as col3) as t MadhivananFailing to plan is Planning to fail
oh...didnt realise they were aliases. thanks for pointing out |
 |
|
|
|
|