Dynamic query with join problem

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)

Dynamic query with join problem

Author

Topic

Javed409
Starting Member

2 Posts

Posted - 2010-11-29 : 02:04:49

I have two tables tableA(ID, Name) and tableB(ID, TEMPName bit)
In tableB TEMPName column name is of format TEMP<values present in Name column of tableA> and TEMPName is of type bit
so possible values are 0 and 1 only.

I have to take join of tableA and tableB on ID and Names related to those IDs in both tables. How can I do this?

e.g.

tableA
ID Name
1 John
2 Sam
3 Mack

tableB
ID TEMPJohn TEMPSam TEMPMack
1 1 0 0
2 0 1 0
3 0 0 1

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2010-11-29 : 02:30:09

Is this what are you expecting.

declare @table table
(
id tinyint,
Name varchar(100)
)

insert into @table
values(1,'John'),(2,'Sam'),(3,'Mack')

select t1.id,[TempJohn]=t1.John,[TempSam]=t1.Sam,[TempMack]=t1.Mack
from
(select * from @table)t
pivot(count(name) for name in ([John],[Sam],[Mack])) t1

Iam a slow walker but i never walk back

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2010-11-29 : 02:39:07

declare @table table
(
id tinyint,
Name varchar(100)
)

insert into @table
values(1,'John'),(2,'Sam'),(3,'Mack')

select table1.* from
(
select id,[TempJohn]=SUM(case when name='john' then 1 else 0 end)
,[TempSam]=SUM(case when name='Sam' then 1 else 0 end)
,[TempMack]=SUM(case when name='Mack' then 1 else 0 end)
from @table tab
group by id
)table1
join
(
select t1.id,[TempJohn]=t1.John,[TempSam]=t1.Sam,[TempMack]=t1.Mack
from
(select * from @table)t
pivot(count(name) for name in ([John],[Sam],[Mack])) t1
)table2 -- this is table 2 since i have not created table2
on table1.id = table2.id and table1.TempJohn = table2.TempJohn and table1.TempMack=table2.TempMack
and table1.TempSam = table2.TempSam

Iam a slow walker but i never walk back

Javed409
Starting Member

2 Posts

Posted - 2010-11-29 : 02:42:08

select * from tableA A inner join tableB B on A.ID = B.ID and
A.Name = B.TEMPName

how to make this possible with given scenarios is the issue

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-11-29 : 02:56:25

Dynamic Query for your requirment
For any number of rows in tableA


CREATE TABLE tableA (ID int, Name varchar(30) )
GO
INSERT INTO tableA 
SELECT 1, 'John' UNION ALL
SELECT 2, 'Sam' UNION ALL
SELECT 3, 'Mack'
GO

CREATE TABLE #Name ( TName VARCHAR(30) )
G0

INSERT INTO #Name
SELECT Distinct Name FROM TableA

DECLARE @SQL VARCHAR(MAX), @Condition AS VARCHAR(MAX)
SELECT @SQL = 'SELECT ID, ', @Condition = ''

SELECT @Condition = @Condition + 
			+ 'CASE WHEN Name = ''' + TName 
			+ ''' THEN 1 ELSE 0 END AS ''TEMP'+ TName + ''', ' + CHAR(13)
				FROM #Name

SELECT @Condition = LEFT(@Condition, LEN(@Condition) - 3 )

EXEC ( @SQL + @Condition + ' FROM TableA' )

Vaibhav T

If I cant go back, I want to go fast...

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-11-29 : 03:00:51

quote:
Originally posted by dineshrajan_it

declare @table table
(
id tinyint,
Name varchar(100)
)

insert into @table
values(1,'John'),(2,'Sam'),(3,'Mack')

select table1.* from
(
select id,[TempJohn]=SUM(case when name='john' then 1 else 0 end)
,[TempSam]=SUM(case when name='Sam' then 1 else 0 end)
,[TempMack]=SUM(case when name='Mack' then 1 else 0 end)
from @table tab
group by id
)table1
join
(
select t1.id,[TempJohn]=t1.John,[TempSam]=t1.Sam,[TempMack]=t1.Mack
from
(select * from @table)t
pivot(count(name) for name in ([John],[Sam],[Mack])) t1
)table2 -- this is table 2 since i have not created table2
on table1.id = table2.id and table1.TempJohn = table2.TempJohn and table1.TempMack=table2.TempMack
and table1.TempSam = table2.TempSam

Iam a slow walker but i never walk back

I think Sum in your query might make value greater than 1
and requirement is that temp columns are of type bit.

Vaibhav T

If I cant go back, I want to go fast...

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2010-11-29 : 03:33:14

I think Sum in your query might make value greater than 1
and requirement is that temp columns are of type bit.

Vaibhav T

If I cant go back, I want to go fast...
[/quote]

Yes you are right. I didn't notice bit value. anyway first derived table containing cross tab queries can be replaced with pivot concept to overcome the sum issue.

Iam a slow walker but i never walk back

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2010-11-29 : 05:18:11

i think this will be helpful to ur req.

select * from @table table1
join
(
select unpvt.id,unpvt.Result,[Name]= substring(unpvt.Name,5,LEN(unpvt.Name)) from
(select t1.id,[TempJohn]=t1.John,[TempSam]=t1.Sam,[TempMack]=t1.Mack
from
(select * from @table)t
pivot(count(name) for name in ([John],[Sam],[Mack])) t1)t
unpivot(Result for Name in ([TempJohn],[TempSam],[TempMack])
) unpvt
)table2 -- this is table 2 since i have not created table2
on table1.id = table2.id and table1.Name = table2.Name

Iam a slow walker but i never walk back

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources