As a work-around and I hope this helps but the key here is you must dump the result sets into a temp tables. In the stored procedure I usually use...

Look up composable DML on this site/google. Something like this should work but would be a major help if we had sample data.

INSERT @NewPolicyEventRiskUnits

SELECT PolicyEventTrID,StagingPolicyRiskUnitTrID,PolicyRiskUnitTrID

FROM( MERGE Policy.PolicyRiskUnit AS Target

...

We have a indexed table in our database called Tally but on the fly I usually just cross join the sys.all_columns tables multiple times. My solution is below.

DECLARE @INT BIGINT...

Great point guys. Here is a better solution with dynamic sql.

IF OBJECT_ID('tempdb..#basetable') IS NOT NULL

DROP TABLE #basetable

CREATE TABLE #basetable

(

caseid INT,

formname VARCHAR(255),

name VARCHAR(255),

value VARCHAR(255)

)

DECLARE @sql VARCHAR(MAX)

DECLARE @cols VARCHAR(MAX)

DECLARE @joins VARCHAR(MAX)

INSERT #basetable(caseid,formname,name,value)

VALUES

(1,...

Try this too: DECLARE @tab TABLE

( ID INT , ColValue INT)

;

INSERT INTO @tab (ID, ColValue)

SELECT...

Nevermind...my solution was wrong 🙁

I was bored but this should work:

IF OBJECT_ID('tempdb..#basetable') IS NOT NULL

DROP TABLE #basetable

CREATE TABLE #basetable

(

caseid INT,

formname VARCHAR(255),

name VARCHAR(255),

value VARCHAR(255)

)

DECLARE @sql VARCHAR(MAX)

DECLARE @cols VARCHAR(MAX)

DECLARE @joins VARCHAR(MAX)

INSERT #basetable(caseid,formname,name,value)

VALUES

(1, 'Highways', 'roadname', 'darlington road'),

(1,...

It is probably coming from the TVF dbo.fn_get_attrib_guid_from_xml. Have you stepped through this because I believe the rest of your code looks fine?

This is getting good.

I understand both sides of this argument. Now sure if there is an correct answer. 😉

I think you may end up with some type of union no matter what but here is my solution...Change DENSE_RANK to RANK if you need to.

DECLARE @PRODUCTS TABLE (

Product_id int

,ProductName...

Here is my final attempt...You probably want to re-check your test data and desired results to see if everything is sync'd properly.

;WITH CTE

AS (

SELECT

c1.Case_ID,

c1.Legal_Omn,

c1.PPL_Area,

Review_Status =

CASE

WHEN c1.Review_Status NOT IN...

Based on the explanation,testdata and your desired results...why would WA_TENANT be in the column for 30-60 days for "Active"? Look at your test data:

UNION ALL SELECT 220, 'Civil', 'WA_Tenant', 5,...

After reading this thread I'm not even close to being clear as to what you want but here is my interpretation. If there duplicate review statuses for a ppl_area(tenant)...I choose...

Below is code using Grouping Sets SSRS 2008...1 table scan

DECLARE @Case_Review TABLE

(Case_ID int, Legal_Omn varchar(30),PPL_Area varchar(30),Revision int

,Review_Status varchar(30),Change_Date datetime);

insert into @Case_Review

SELECT 220, 'Civil', 'WA_Tenant', 1, 'Proposed', '2012-04-17 12:17'

UNION ALL SELECT...

Sorry, corrected the original post. DMV = Dynamic Management View