Need help with 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 2008 Forums

Transact-SQL (2008)

Need help with Query

Author

Topic

Sanatan
Starting Member

19 Posts

Posted - 2011-07-25 : 23:53:37

Hi I need help with building a query that needs the following result set. I have two tables as below.

Table1
Col1 Col2
A1 my strA
A1 my strB
A1 my strB
A1 my strOnly
A2 my strA
A2 my strB
A3 my strC
A3 my strC
A3 my strOnly

Table2
Col1 Col2
strA AA
strB BB
strC CC

When user queries for the Table1:Col1 value (e.g. A1, A2 etc), the return value should be a Group By of Table2:Col2 and it's count as below.

When user queries for A1, return value should be AA,1; BB,2; Misc,1
When user queries for A2, return value should be AA,1; BB,1
When user queries for A3, return value should be CC,2; Misc,1

Can someone please assist with this?

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-07-26 : 02:53:00

[code]
create table #Table1
(
Col1 varchar(20),
Col2 varchar(20)
)
go
insert into #Table1
select 'A1','strA' union all
select 'A1','strB' union all
select 'A1','strB' union all
select 'A1','strOnly' union all
select 'A2','strA' union all
select 'A2','strB' union all
select 'A3','strC' union all
select 'A3','strC' union all
select 'A3','strOnly'
go
create table #Table2
(
Col1 varchar(20),
Col2 varchar(20)
)
go
insert into #Table2
select 'strA','AA' union all
select 'strB','BB' union all
select 'strC','CC'
go

with cte as
(
select
t1.Col1 as Col1
,count(t1.Col2) as cnt
,isnull(t2.Col2,'Misc') as Col2
from #Table1 t1
left join #Table2 t2 on t1.Col2=t2.Col1
group by t1.Col1,t2.Col2
--order by t1.Col1
)
select
Col1
,Col2+','+convert(varchar(10),cnt) as Col2
from cte
order by Col1
[/code]

--Ranjit

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-07-26 : 03:11:36

quote:

When user queries for A1, return value should be AA,1; BB,2; Misc,1
When user queries for A2, return value should be AA,1; BB,1
When user queries for A3, return value should be CC,2; Misc,1


with cte as
(
select 
	 t1.Col1 as Col1
	,count(t1.Col2) as cnt
	,isnull(t2.Col2,'Misc')  as Col2
from #Table1 t1
left join #Table2   t2 on t1.Col2=t2.Col1
group by t1.Col1,t2.Col2
--order by t1.Col1
)
select 
	col1
	,convert(varchar(max),substring((select ( '; ' +col2+','+convert(varchar(10),cnt))
		from  cte c2      
		where c1.col1=c2.col1      
		order by col2      
		for xml path( '' )      
                  ), 3, 1000 )) as col2  
from cte c1
group by col1

--Ranjit

Sanatan
Starting Member

19 Posts

Posted - 2011-07-26 : 11:10:52

Hi, thanks for the reply. One more follow-up question...

The t2.Col1 values will be an substring of t1.col2. Though I specified this in the data but did not mention explicitly. It will not follow the t1.Col2=t2.Col1 as you wrote in the CTE.

Can you help with how to write this contains clause in the CTE?

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-07-27 : 05:03:10

quote:
[i]

Table1
Col1 Col2
A1 my strA
A1 my strB

Use appropriate String function.. like below
substring(t1.Col2,4,len(t1.Col2))=t2.Col1

--Ranjit

sp22403
Starting Member

3 Posts

Posted - 2011-07-27 : 07:11:31

Actually I had the need to ensure the T2.Col1 present in T1.Col2 string. So I used CHARINDEX and the CTE worked like a charm.

Thanks for your quick help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources