SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query help


Query help

Author
Message
s_osborne2
s_osborne2
SSC Eights!
SSC Eights! (997 reputation)SSC Eights! (997 reputation)SSC Eights! (997 reputation)SSC Eights! (997 reputation)SSC Eights! (997 reputation)SSC Eights! (997 reputation)SSC Eights! (997 reputation)SSC Eights! (997 reputation)

Group: General Forum Members
Points: 997 Visits: 2292
Hi,

I have a query that returns a ProposalId a RiskId and RiskTaken bit flag. I've setup some test data below:

DECLARE @table TABLE
(
ProposalId INT,
RiskId INT,
RiskTaken BIT
)
INSERT INTO @table
SELECT 1, 1, 1
UNION ALL SELECT 1,2,0
UNION ALL SELECT 1,3,0
UNION ALL SELECT 2,1,0
UNION ALL SELECT 2,2,0
UNION ALL SELECT 2,3,0

SELECT
t.[ProposalId],
t.[RiskId],
t.[RiskTaken]
FROM
@table t



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,

Simon



MCSE: Data Platform
MCSE: Business Intelligence
Follow me on Twitter: @WazzTheBadger
LinkedIn Profile: Simon Osborne
Attachments
Results.png (13 views, 6.00 KB)
higgim
higgim
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1108 Visits: 2622
DECLARE @table TABLE
(
ProposalId INT,
RiskId INT,
RiskTaken BIT
)
INSERT INTO @table
SELECT 1, 1, 1
UNION ALL SELECT 1,2,0
UNION ALL SELECT 1,3,0
UNION ALL SELECT 2,1,0
UNION ALL SELECT 2,2,0
UNION ALL SELECT 2,3,0
;
WITH ProposalRisk
AS
(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



Looks like it should work
s_osborne2
s_osborne2
SSC Eights!
SSC Eights! (997 reputation)SSC Eights! (997 reputation)SSC Eights! (997 reputation)SSC Eights! (997 reputation)SSC Eights! (997 reputation)SSC Eights! (997 reputation)SSC Eights! (997 reputation)SSC Eights! (997 reputation)

Group: General Forum Members
Points: 997 Visits: 2292
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



MCSE: Data Platform
MCSE: Business Intelligence
Follow me on Twitter: @WazzTheBadger
LinkedIn Profile: Simon Osborne
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25892 Visits: 17515
s_osborne2 (1/23/2013)
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


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.


DECLARE @table TABLE
(
ProposalId INT,
RiskId INT,
RiskTaken BIT
)
INSERT INTO @table
SELECT 1, 1, 1
UNION ALL SELECT 1,2,0
UNION ALL SELECT 1,3,0
UNION ALL SELECT 2,1,0
UNION ALL SELECT 2,2,0
UNION 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




_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
s_osborne2
s_osborne2
SSC Eights!
SSC Eights! (997 reputation)SSC Eights! (997 reputation)SSC Eights! (997 reputation)SSC Eights! (997 reputation)SSC Eights! (997 reputation)SSC Eights! (997 reputation)SSC Eights! (997 reputation)SSC Eights! (997 reputation)

Group: General Forum Members
Points: 997 Visits: 2292
Absolutely spot on. Nice one Sean.

Thanks,

Simon



MCSE: Data Platform
MCSE: Business Intelligence
Follow me on Twitter: @WazzTheBadger
LinkedIn Profile: Simon Osborne
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25892 Visits: 17515
The kudos belongs to Michael Higgins. He provided the solution. I just suggested a way for you to fit it in with your system. :-D

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search