June 4, 2010 at 7:28 am
Hi,
I have written a CTE query that lists multiple insurance companies per OrderID in the one cell (row) instead of multiple rows. The original data looks something like this:
OrderID Insurance Companies
1 A
1 B
1 C
1 D
2 Z
2 X
2 Y
3 AA
3 BB
The transformed data looks like this:
OrderID Insurance Companies
1 A, B, C, D
2 Z, X, Y
3 AA, BB
The query syntax looks similar to this:
SELECT DISTINCT
o.OrderID AS OrderID,
InsuranceName
INTO #PAYER
FROM Orders;
WITH CTE(OrderID, Insurance_List, Insurance_Name, [Length])
AS
(SELECT
OrderID,
CAST('' AS varchar(8000)),
CAST('' AS varchar(8000)),
0
FROM
#PAYER
GROUP BY
OrderID
UNION ALL
SELECT
b.OrderID,
CAST(Insurance_List + CASE WHEN [Length] = 0 THEN '' ELSE ', ' END AS varchar(8000)),
CAST(b.InsuranceName AS VARCHAR(8000)),
[Length] + 1
FROM
CTE d
JOIN
#PAYER b
ON b.OrderID = d.OrderID
WHERE
(b.InsuranceName > d.Insurance_Name)
)
SELECT DISTINCT
OrderID,
Insurance_List
FROM
(SELECT
OrderID,
Insurance_List,
RANK() OVER (PARTITION BY OrderID ORDER BY [Length] DESC)
FROM
CTE) x
(OrderID, Insurance_List, Rankz)
WHERE
Rankz = 1
ORDER BY
OrderID
DROP TABLE #PAYER
------------------------------------------------------------------------------------------------
Does anyone know how to do the following (?):
•If the number of insurances is > 3 for a given OrderID, only include the first 3?
•If the number of insurances is < 3, put blanks in the fields as placeholders where there is no data.
Thanks in advance for the help.
June 4, 2010 at 7:56 am
Please provide create table and test data insert scripts in order to setup your case.
June 4, 2010 at 8:01 am
MattW2010 (6/4/2010)
------------------------------------------------------------------------------------------------Does anyone know how to do the following (?):
•If the number of insurances is > 3 for a given OrderID, only include the first 3?
•If the number of insurances is < 3, put blanks in the fields as placeholders where there is no data.
Thanks in advance for the help.
Sounds like an easy job when you use the ROW_NUMBER() windowing function. See this article[/url] for more information.
Also, to elaborate on what Eugene said, please take a look at the first link in my signature.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 4, 2010 at 8:11 am
Thanks for the direction...
create table #Orders
(
OrderID int,
InsuranceName varchar(10)
)
insert into #Orders
select 1, 'A' union
select 1, 'B' union
select 1, 'C' union
select 1, 'D' union
select 2, 'Z' union
select 2, 'X' union
select 2, 'Y' union
select 3, 'AA' union
select 3, 'AB'
SELECT DISTINCT
OrderID AS OrderID,
InsuranceName
INTO #PAYER
FROM #Orders;
WITH CTE(OrderID, Insurance_List, Insurance_Name, [Length])
AS
(SELECT
OrderID,
CAST('' AS varchar(8000)),
CAST('' AS varchar(8000)),
0
FROM
#PAYER
GROUP BY
OrderID
UNION ALL
SELECT
b.OrderID,
CAST(Insurance_List + CASE WHEN [Length] = 0 THEN '' ELSE ', ' END + b.InsuranceName AS varchar(8000)),
CAST(b.InsuranceName AS VARCHAR(8000)),
[Length] + 1
FROM
CTE d
JOIN
#PAYER b
ON b.OrderID = d.OrderID
WHERE
(b.InsuranceName > d.Insurance_Name)
)
SELECT DISTINCT
OrderID,
Insurance_List
FROM
(SELECT
OrderID,
Insurance_List,
RANK() OVER (PARTITION BY OrderID ORDER BY [Length] DESC)
FROM
CTE) x
(OrderID, Insurance_List, Rankz)
WHERE
Rankz = 1
ORDER BY
OrderID
DROP TABLE #PAYER
DROP TABLE #Orders
June 4, 2010 at 8:14 am
I am familiar with the row number function and can see how that would help when there are instances of 3 or more insurances.
But how would you account for the orders that only have 2 insurances? How would I add the extra blank space?
Thanks,
Matt
June 4, 2010 at 8:49 am
Conceptually, I think I have it. Thanks for the replies guys!
/*******************************************************************************************
Creates temporary Order Table for example
*******************************************************************************************/
create table #Orders
(
OrderID int,
InsuranceName varchar(10)
)
insert into #Orders
select 1, 'A' union
select 1, 'B' union
select 1, 'C' union
select 1, 'D' union
select 2, 'Z' union
select 2, 'X' union
select 2, 'Y' union
select 3, 'AA' union
select 3, 'AB' union
select 4, 'Bears Rule'
/*******************************************************************************************
Creates temporary Orders table that includes row_number() partition by
*******************************************************************************************/
SELECT *, ROW_NUMBER() OVER(Partition By OrderID Order By OrderID, InsuranceName) AS PayerPartition
INTO #OrdersII
FROM #Orders
/*******************************************************************************************
Scenario 1 = OrderID has 3 insurances tied to it
*******************************************************************************************/
SELECT OrderID, InsuranceName, PayerPartition
INTO #OrdersIII
FROM #OrdersII o
WHERE (SELECT MAX(PayerPartition) FROM #OrdersII WHERE OrderID = o.OrderID) = 3
UNION ALL
/*******************************************************************************************
Scenario 2 = OrderID has more than 3 insurances tied to it
*******************************************************************************************/
SELECT *
FROM (
SELECT OrderID, InsuranceName, PayerPartition
FROM #OrdersII o
WHERE (SELECT MAX(PayerPartition) FROM #OrdersII WHERE OrderID = o.OrderID) > 3
) A
WHERE PayerPartition <= 3
UNION ALL
/*******************************************************************************************
Scenario 3 = OrderID has only 2 insurances tied to it
*******************************************************************************************/
SELECT OrderID, InsuranceName, PayerPartition
FROM #OrdersII o
WHERE (SELECT MAX(PayerPartition) FROM #OrdersII WHERE OrderID = o.OrderID) = 2
UNION ALL
SELECT distinct OrderID, 'Blank', 3
FROM #OrdersII o
WHERE (SELECT MAX(PayerPartition) FROM #OrdersII WHERE OrderID = o.OrderID) = 2
UNION ALL
/*******************************************************************************************
Scenario 4 = OrderID has only 1 insurance tied to it
*******************************************************************************************/
SELECT OrderID, InsuranceName, PayerPartition
FROM #OrdersII o
WHERE (SELECT MAX(PayerPartition) FROM #OrdersII WHERE OrderID = o.OrderID) = 1
UNION ALL
SELECT distinct OrderID, 'Blank', 2
FROM #OrdersII o
WHERE (SELECT MAX(PayerPartition) FROM #OrdersII WHERE OrderID = o.OrderID) = 1
UNION ALL
SELECT distinct OrderID, 'Blank2', 3
FROM #OrdersII o
WHERE (SELECT MAX(PayerPartition) FROM #OrdersII WHERE OrderID = o.OrderID) = 1
/*******************************************************************************************
CTE query that updates Insurances so that they are on one line (row)
*******************************************************************************************/
SELECT OrderID AS OrderID,
InsuranceName
INTO #PAYER
FROM #OrdersIII;
WITH CTE(OrderID, Insurance_List, Insurance_Name, [Length])
AS
(SELECT
OrderID,
CAST('' AS varchar(8000)),
CAST('' AS varchar(8000)),
0
FROM
#PAYER
GROUP BY
OrderID
UNION ALL
SELECT
b.OrderID,
CAST(Insurance_List + CASE WHEN [Length] = 0 THEN ' ' ELSE ', ' END + b.InsuranceName AS varchar(8000)),
CAST(b.InsuranceName AS VARCHAR(8000)),
[Length] + 1
FROM
CTE d
JOIN
#PAYER b
ON b.OrderID = d.OrderID
WHERE
(b.InsuranceName > d.Insurance_Name)
)
SELECT DISTINCT
OrderID,
Insurance_List
FROM
(SELECT
OrderID,
Insurance_List,
RANK() OVER (PARTITION BY OrderID ORDER BY [Length] DESC)
FROM
CTE) x
(OrderID, Insurance_List, Rankz)
WHERE
Rankz = 1
ORDER BY
OrderID
DROP TABLE #PAYER
DROP TABLE #Orders
DROP TABLE #OrdersII
DROP TABLE #OrdersIII
June 4, 2010 at 9:04 am
You might also be able to simplify it like this:
-- get the first three, if there are that many
SELECT OrderID, InsuranceName, PayerPartition
FROM #OrdersII o
WHERE PayerPartition <= 3
UNION ALL
-- add a blank in position 2 if only 1
SELECT distinct OrderID, 'Blank2', 2
FROM #OrdersII o
WHERE (SELECT MAX(PayerPartition) FROM #OrdersII WHERE OrderID = o.OrderID) = 1
UNION ALL
-- add a blank in position 3 if < 3 (will get if 1 or 2)
SELECT distinct OrderID, 'Blank3', 3
FROM #OrdersII o
WHERE (SELECT MAX(PayerPartition) FROM #OrdersII WHERE OrderID = o.OrderID) < 3
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 4, 2010 at 9:15 am
Beautiful, thanks!!!! That works too!
June 4, 2010 at 9:37 am
This will not need temp tables and will do similar job:
;WITH PP
AS
(
SELECT OrderID, InsuranceName, ROW_NUMBER() OVER(Partition By OrderID Order By OrderID, InsuranceName) AS PayerPartition
FROM #Orders
)
, blnks
AS
(
SELECT TT.PP, Ord.OrderID
FROM (SELECT 1 AS PP UNION SELECT 2 UNION SELECT 3) TT
CROSS JOIN (SELECT DISTINCT OrderID FROM #Orders) Ord
)
, fin
AS
(
SELECT b.PP, b.OrderID, ISNULL(p.InsuranceName, 'Blank') as InsuranceName
FROM blnks b
LEFT JOIN PP p on b.PP = p.PayerPartition and b.OrderID = p.OrderID
)
SELECT f1.OrderID, f1.InsuranceName + ', ' + f2.InsuranceName + ', ' + f3.InsuranceName
FROM fin f1
JOIN fin f2 ON f2.OrderID = f1.OrderID and f2.PP = 2
JOIN fin f3 ON f3.OrderID = f1.OrderID and f3.PP = 3
WHERE f1.PP = 1
Do you really need to have "Blank1", "Blank2" or just a "Blank" is ok?
June 4, 2010 at 9:40 am
Blank is fine. In fact, the reason I put "Blank" in there at all is because it didn't work when I just used a space (' ').
What I really want is a space and not the actual word. Any chance you can work that in there as well?
Thanks.
June 4, 2010 at 9:56 am
Do you want a real blank or a space? Using varchar you can have the space in the middle of string like ', ,' but not in the end as ', , '. If you happy with just blank your result will look like: 'A, ,'
(You still have one space between comas as you using ', ')
Replace 'Bank' with '' or with ' ' in the ISNULL function to have desired result.
June 4, 2010 at 10:16 am
Hmmm, I'm not sure I follow. Can you please identify where in the query I would add this syntax? I've tried in multiple places, but the final results still aren't accounting for the blank spaces.
June 4, 2010 at 12:27 pm
Can you specify exactly how you want your results to look like? Do you want comas in results?
Is this what you want?:
;WITH PP
AS
(
SELECT OrderID, InsuranceName, ROW_NUMBER() OVER(Partition By OrderID Order By OrderID, InsuranceName) AS PayerPartition
FROM #Orders
)
, blnks
AS
(
SELECT TT.PP, Ord.OrderID
FROM (SELECT 1 AS PP UNION SELECT 2 UNION SELECT 3) TT
CROSS JOIN (SELECT DISTINCT OrderID FROM #Orders) Ord
)
, fin
AS
(
SELECT b.PP, b.OrderID, ISNULL(p.InsuranceName, '') as InsuranceName
FROM blnks b
LEFT JOIN PP p on b.PP = p.PayerPartition and b.OrderID = p.OrderID
)
SELECT f1.OrderID, f1.InsuranceName + ', ' + f2.InsuranceName + ', ' + f3.InsuranceName
FROM fin f1
JOIN fin f2 ON f2.OrderID = f1.OrderID and f2.PP = 2
JOIN fin f3 ON f3.OrderID = f1.OrderID and f3.PP = 3
WHERE f1.PP = 1
June 4, 2010 at 12:31 pm
That's exactly what I'm looking for, thank you.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply