2 select into one record - 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)

2 select into one record

Author

Topic

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2011-12-12 : 15:49:06

Select ecv.Value AS SS1
From EntityChangeSet as ecs
Inner Join EntityChangeValue as ecv On ecs.EntityChangeSetID = ecv.EntityChangeSetID
Where ecs.OffenderChangeSummaryID=1729 AND ecs.TableID=17 AND ecv.ColumnID=176

brings back 111667777
Select (select PASORTdbo.DecryptIt(ecv.Value, N'asdf0123', 0) AS Expr1) AS SSN
From EntityChangeSet as SS2
Inner Join EntityChangeValue as ecv On ecs.EntityChangeSetID = ecv.EntityChangeSetID
Where ecs.OffenderChangeSummaryID=1729 AND ecs.TableID=17 AND ecv.ColumnID=177

brings back 111223333

I need this to be in one record

SS1 SS2
111667777 111223333

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-12-12 : 19:26:46

Select ecv.Value AS SS1
From EntityChangeSet as ecs
Inner Join EntityChangeValue as ecv On ecs.EntityChangeSetID = ecv.EntityChangeSetID
Where ecs.OffenderChangeSummaryID=1729 AND ecs.TableID=17 AND ecv.ColumnID=176

CROSS JOIN

Select (select PASORTdbo.DecryptIt(ecv.Value, N'asdf0123', 0) AS Expr1) AS SSN
From EntityChangeSet as SS2
Inner Join EntityChangeValue as ecv On ecs.EntityChangeSetID = ecv.EntityChangeSetID
Where ecs.OffenderChangeSummaryID=1729 AND ecs.TableID=17 AND ecv.ColumnID=177

=======================================
Faced with the choice between changing one's mind and proving that there is no need to do so, almost everyone gets busy on the proof. -John Kenneth Galbraith

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2011-12-13 : 08:45:47

This does not work

Dave
Helixpoint Web Development
http://www.helixpoint.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 08:49:49

[code]
SELECT MAX(CASE WHEN ecv.ColumnID=176 THEN ecv.Value ELSE NULL END) AS SS1,
MAX(CASE WHEN ecv.ColumnID=177 THEN PASORTdbo.DecryptIt(ecv.Value, N'asdf0123', 0) ELSE NULL END) AS SSN
From EntityChangeSet as SS2
Inner Join EntityChangeValue as ecv On ecs.EntityChangeSetID = ecv.EntityChangeSetID
Where ecs.OffenderChangeSummaryID=1729 AND ecs.TableID=17
[/code]

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

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2011-12-13 : 09:18:05

What is 'ecs'?

Dave
Helixpoint Web Development
http://www.helixpoint.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 09:29:05

it should be ecv


SELECT MAX(CASE WHEN ecv.ColumnID=176  THEN ecv.Value ELSE NULL END) AS SS1,
MAX(CASE WHEN ecv.ColumnID=177  THEN PASORTdbo.DecryptIt(ecv.Value, N'asdf0123', 0) ELSE NULL END) AS SSN
From EntityChangeSet as SS2
Inner Join EntityChangeValue as ecv On SS2.EntityChangeSetID = ecv.EntityChangeSetID
Where ecv.OffenderChangeSummaryID=1729 AND ecv.TableID=17

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources