Data's TSQL 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)

Data's TSQL Query

Author

Topic

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-08-23 : 11:06:45

Hi all : )
I have data like below in my table


id   name   values
1   test1   xyz + xyz + ppp + xyz + ccc + ppp
2   test2   xyz + xyz + ccc
and so on..

I want output like..


id   name   values
1   test1   3 X xyz, 2 X ppp, ccc
2   test2   2 X xyz + ccc
and so on..

Please help..
thank you all

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-23 : 12:20:22

will the character sets be always among these ie xyz,ppp and ccc?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-08-23 : 13:00:59

quote:
Originally posted by visakh16

will the character sets be always among these ie xyz,ppp and ccc?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

thank you visakh : )
no, it will be any value.. just it may be repeatative or single..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-23 : 13:18:03

[code]
;with cte as
(
select t.id,[name],ltrim(rtrim(f.val)) as val,cast(count(*) as varchar(10)) as cnt
from tbl t
cross apply dbo.ParseValues([value],' + ')f
group by t.id,[name],ltrim(rtrim(f.val))
)

select distinct id,[name],
stuff((select ',' + case when cnt > 1 then cnt + ' X ' else '' end + val
from cte where id=t.id and [name]=t.[name]
order by cnt desc
for xml path('')),1,1,'')
from cte t
[/code]

parse values can be found here

http://visakhm.blogspot.com/2010/02/parsing-delimited-string.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-08-23 : 13:43:23

quote:
Originally posted by visakh16

;with cte as
(
select t.id,[name],ltrim(rtrim(f.val)) as val,cast(count(*) as varchar(10)) as cnt
from tbl t
cross apply dbo.ParseValues([value],' + ')f
group by t.id,[name],ltrim(rtrim(f.val))
)

select distinct id,[name],
stuff((select ',' + case when cnt > 1 then cnt + ' X ' else '' end + val
from cte where id=t.id and [name]=t.[name]
order by cnt desc
for xml path('')),1,1,'')
from cte t

parse values can be found here

http://visakhm.blogspot.com/2010/02/parsing-delimited-string.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

GREAT THANKS Visakh..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-23 : 13:46:56

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-08-23 : 14:02:44

I have another table with same kind of summarized data but with diff. rows..


id   name   values
1   test1   xyz
1   test1   xyz
1   test1   ppp
1   test1   xyz 
1   test1   ccc
1   test1   ppp
2   test2   xyz
2   test2   xyz
2   test2   ccc
and so on..

i created query for same output like previous one
but it returns with multiple rows

select distinct name, values
from (
select
name, cast(count(values) over (partition by values) as varchar(10)) + ' X ' + values as values from tbl2
) t

can you please give inputs on above one ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-23 : 14:12:17

try like this


select name, values
from (
select distinct  
name, cast(count(values) over (partition by values) as varchar(10)) + ' X ' + values as values from tbl2
) t

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-08-23 : 14:24:11

Yes, but it returns all diff. row for same category..
i want all comma seperated for each category..

like..


name    values
test1	2 X ccc, 2 X ppp, 5 X xyz
test2	2 X ccc, 5 X xyz

thanks visakh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-23 : 14:25:36

for that you need to use STUFF with FOR XML PATH('') as i showed earlier

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-08-23 : 14:57:38

quote:
Originally posted by visakh16

for that you need to use STUFF with FOR XML PATH('') as i showed earlier

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Hi it worked for me.. but count over seems invalid value there

below is my query..

;with cte as
(
select distinct id,
[name], [values],
count([values]) over (partition by id) as cnt from tab3
)

select distinct name ,
stuff((select ',' + case when cnt > 1 then cast(cnt as varchar(10)) + ' X ' else '' end + [values]
from cte where [name]=t.[name]
order by cnt desc
for xml path('')),1,1,'')
from cte t

and output comes.. like
name Values column
-----------------------
test1 6 X ccc,6 X ppp,6 X xyz
test2 5 X ccc,5 X poris,5 X xyz

but actual value in table are like..


test1	xyz	1
test1	xyz	1
test1	ppp	1
test1	xyz	1
test1	ccc	1
test1	ppp	1
test2	xyz	2
test2	xyz	2
test2	ccc	2
test2	poris	2
test2	poris	2
test3	xyz	3

why count over returns sum ..of all below rows.. i used partition ..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-23 : 15:06:37

it should be count([values]) over (partition by id,name) i guess

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-08-23 : 15:11:01

quote:
Originally posted by visakh16

it should be count([values]) over (partition by id,name) i guess

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Below is query
;with cte as
(
select distinct id,
[name], [values],
count([values]) over(partition by id, [name]) as cnt from tab3
)
select * from cte order by [name]

It returns invalid data.. as below..


1	test1	ccc	6
1	test1	ppp	6
1	test1	xyz	6
2	test2	ccc	5
2	test2	poris	5
2	test2	xyz	5
3	test3	jnalis	12
3	test3	xyz	12

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-08-23 : 15:20:29

its done..
i am doing wrong partition.. for count..

;with cte as
(
select distinct id,
[name], [values],
count([id]) over(partition by [name], [values]) as cnt from tab3
)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-23 : 15:20:59

hmm.. glad that you sorted it out

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources