InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 17:55:08 GMT20http://www.sqlservercentral.com/Forums/Topic1410579-392-1.aspxThe kudos belongs to Michael Higgins. He provided the solution. I just suggested a way for you to fit it in with your system. :-DWed, 23 Jan 2013 09:55:00 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1410579-392-1.aspxAbsolutely spot on. Nice one Sean.Thanks,SimonWed, 23 Jan 2013 09:51:18 GMTs_osborne2http://www.sqlservercentral.com/Forums/Topic1410579-392-1.aspx[quote][b]s_osborne2 (1/23/2013)[/b][hr]Hi,Thanks for you help.I've been playing with this, and you're right it does work but doesn't give me the solution to the problem i have. I've given @table as an example here to illustrate the problem. In my real world problem @table is actually the result of a complex query and isn't stored in a table (temporary or otherwise). I have many more columns than outlined above and the query returns ~40k rows.I was hoping to be able to do something in-line with a CROSS APPLY type solution but haven't been able to crack it. I could write the result of the query to a temporary or even a persisted table and then use the CTE as you suggest but there's write overhead invovled with that, that i'd like to avoid if possible.Is there a way to do this without a CTE or am I hoping for a solution that simply isn't possible?Thanks,Simon[/quote]Actually the way you describe this a CTE is exactly what you want. If you use a cross apply you would have to requery your data anyway. This is exactly what CTEs were designed for.Here is the same solution but instead of selecting directly from the table variable I setup a CTE. You would just replace the first CTE query with your complex query.[code]DECLARE @table TABLE( ProposalId INT, RiskId INT, RiskTaken BIT)INSERT INTO @tableSELECT 1, 1, 1UNION ALL SELECT 1,2,0UNION ALL SELECT 1,3,0UNION ALL SELECT 2,1,0UNION ALL SELECT 2,2,0UNION ALL SELECT 2,3,0;with CTE_AsQueryReplacement as( select * from @table --replace this with your complex query),ProposalRisk AS(SELECT DISTINCT ProposalId FROM CTE_AsQueryReplacement WHERE RiskTaken = 1)SELECT t.[ProposalId], t.[RiskId], t.[RiskTaken], CASE WHEN pr.ProposalId IS NOT NULL THEN 1 ELSE 0 END AS ProposalTaken FROM CTE_AsQueryReplacement t LEFT OUTER JOIN ProposalRisk pr ON t.ProposalId = pr.ProposalId [/code]Wed, 23 Jan 2013 09:33:26 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1410579-392-1.aspxHi,Thanks for you help.I've been playing with this, and you're right it does work but doesn't give me the solution to the problem i have. I've given @table as an example here to illustrate the problem. In my real world problem @table is actually the result of a complex query and isn't stored in a table (temporary or otherwise). I have many more columns than outlined above and the query returns ~40k rows.I was hoping to be able to do something in-line with a CROSS APPLY type solution but haven't been able to crack it. I could write the result of the query to a temporary or even a persisted table and then use the CTE as you suggest but there's write overhead invovled with that, that i'd like to avoid if possible.Is there a way to do this without a CTE or am I hoping for a solution that simply isn't possible?Thanks,SimonWed, 23 Jan 2013 09:20:56 GMTs_osborne2http://www.sqlservercentral.com/Forums/Topic1410579-392-1.aspx[code="sql"]DECLARE @table TABLE( ProposalId INT, RiskId INT, RiskTaken BIT)INSERT INTO @tableSELECT 1, 1, 1UNION ALL SELECT 1,2,0UNION ALL SELECT 1,3,0UNION ALL SELECT 2,1,0UNION ALL SELECT 2,2,0UNION ALL SELECT 2,3,0;WITH ProposalRiskAS(SELECT DISTINCT ProposalId FROM @table WHERE RiskTaken = 1)SELECT t.[ProposalId], t.[RiskId], t.[RiskTaken], CASE WHEN pr.ProposalId IS NOT NULL THEN 1 ELSE 0 END AS ProposalTaken FROM @table t LEFT OUTER JOIN ProposalRisk pr ON t.ProposalId = pr.ProposalId[/code]Looks like it should workWed, 23 Jan 2013 07:48:59 GMTmichael.higginshttp://www.sqlservercentral.com/Forums/Topic1410579-392-1.aspxHi,I have a query that returns a ProposalId a RiskId and RiskTaken bit flag. I've setup some test data below:[code="sql"]DECLARE @table TABLE( ProposalId INT, RiskId INT, RiskTaken BIT)INSERT INTO @tableSELECT 1, 1, 1UNION ALL SELECT 1,2,0UNION ALL SELECT 1,3,0UNION ALL SELECT 2,1,0UNION ALL SELECT 2,2,0UNION ALL SELECT 2,3,0SELECT t.[ProposalId], t.[RiskId], t.[RiskTaken] FROM @table t[/code]Now, what'd i'd like is another column called ProposalTaken that should be a 1 where there is a risk for that proposal that has a risktaken flag of 1. Example results shown in the attachment.Is there a way to achieve this?Thanks,SimonWed, 23 Jan 2013 07:39:39 GMTs_osborne2