May 23, 2008 at 2:53 pm
I need to combine information from multiple rows of one table
into one row of a view, to duplicate a table that had originally
been created from old mainframe data.
In the SQL database, one table contains land information, and will have
many land segments for each piece of property. Another table contains
property information, and a third table links the two together. The
mainframe had a fixed limit of how many land segments could be attached
to each property, so this view must be limited to the first six land
segments.
So, I created the following CTE to number the land segments:
WITH Land_CTE (LandLineNumber, UseCode, UnitPrice,
FrontFeet, Property_ID, Land_ID) AS
(SELECT ROW_NUMBER() OVER (PARTITION BY P.Property_ID ORDER BY L.Land_ID),
L.Usecode, L.UnitPrice, L.Frontage, P.Property_ID, L.Land_ID
FROM Property P
JOIN Property2Land P2L ON P2L.Property_ID=P.Property_ID
JOIN Land L ON L.Land_ID = P2L.Land_ID)
Then, in the next statements, I've created multiple subqueries to
retrieve the UseCode and UnitPrice for landline#1 thru landline#6, as
well as one to calculate the total front feet of all landlines associated
with one property. Here's a snippet of that code:
(SELECT SUM(FrontFeet) FROM LAND_CTE
WHERE Property.Property_ID = Land_CTE.Property_ID) AS FRONT_FT,
(SELECT UseCode FROM LAND_CTE WHERE LandLineNumber = 1
AND Property.Property_ID = Land_CTE.Property_ID) AS USE_CODE_1,
(SELECT UnitPrice FROM LAND_CTE WHERE LandLineNumber = 1
AND Property.Property_ID = Land_CTE.Property_ID) AS UNIT_PRICE_1,
(SELECT UseCode FROM LAND_CTE WHERE LandLineNumber = 2
AND Property.Property_ID = Land_CTE.Property_ID) AS USE_CODE_2,
(SELECT UnitPrice FROM LAND_CTE WHERE LandLineNumber = 2
AND Property.Property_ID = Land_CTE.Property_ID) AS UNIT_PRICE_2,
This is generating the information I need in the view, but is there a better
way to do this? (I'm still learning this stuff, so please be gentle!)
--Gwen
May 23, 2008 at 3:19 pm
please post a sample of the desired output. it's hard (for me) to understand what you want.
May 25, 2008 at 12:57 pm
I want to end up with a table that has:
1) Property_ID
2) Sum of front feet of all land segments associated with that property
3) Land use code of first land segment associated with that property
4) Land unit price of first land segment associated with that property
5) Land use code of second land segment associated with that property
6) Land unit price of second land segment associated with that property
7) Land use code of third land segment associated with that property
8) Land unit price of third land segment associated with that property
9) Land use code of fourth land segment associated with that property
10) Land unit price of fourth land segment associated with that property
11) Land use code of fifth land segment associated with that property
12) Land unit price of fifth land segment associated with that property
13) Land use code of sixth land segment associated with that property
14) Land unit price of sixth land segment associated with that property
The use code and unit price of the seventh and greater land segments that may be associated with a property are not included. (The front feet is still included.) And if there are less than six, those will be NULL in the final table.
The land table has a row for every individual land segment. The CTE allowed me to effectively number the land segments per property, and then the subqueries allowed me to pull the specific use code or unit price, based on the row number of the land segment.
I hope that's clearer!
May 27, 2008 at 7:02 am
Have you considered the PIVOT operator? SQL 2005 provides this rather convenient method to convert rows to columns (and UNPIVOT to do the reverse). As an example, let's use your CTE as the source. Also, convert the two values you need from each row into a single character string, e.g.
CAST(Use_Code AS char(20)) + CAST(Front_Feet AS char(20) AS COMBINED_DATA
Then you can do something along the lines of:
SELECT PROPERTY_ID, [1], [2], [3], [4], [5], [6]
FROM CTE
PIVOT (MAX(COMBINED_DATA) FOR ROW_NUM IN ([1],[2],[3],
[4],[5],[6])) AS PVT
Converting back in a subsequent step should be pretty easy, e.g.:
CAST(RTRIM(LEFT([1],20)) AS varchar(nn)) AS USE_CODE_1, CAST(RTRIM(RIGHT([1],20)) AS int) AS FRONT_FEET_1, ...
Look up PIVOT in SQL Books Online and you can see how it operates. It's a lot easier to read and understand the T-SQL using PIVOT or UNPIVOT than to have to go halfway around the block with manual manipulation. Hope that helps.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 27, 2008 at 3:19 pm
Thanks for the suggestion. I'll see what I can do with it. (It's taking me a bit to get my head around this, but I'll be sure to post again to let you know if I got this to work or not.)
And if anyone has additional ideas, I'll take them too!
..Gwen..
May 28, 2008 at 7:52 pm
try this
create table #data ( propertyId int, segmentNum int,
useCode varchar(8), price money, frontage decimal(8,2), acerage decimal(8,2))
insert into #data
select 1, 1, 'A', 16000, 75, 1.25 union
select 1, 2, 'A1', 7000, 25, .8 union
select 1, 3, 'A1', 6000, 75, 1.25 union
select 2, 1, 'B', 3000, 35, .25 union
select 2, 2, 'A1', 2000, 35, .25 union
select 2, 3, 'A1', 2000, 35, .25 union
select 2, 4, 'B2', 2000, 35, .25 union
select 2, 5, 'B3', 2000, 35, .25 union
select 2, 6, 'B4', 3500, 45, .35 union
select 2, 7, 'B5', 3000, 45, .30 union
select 3, 1, 'A1', 8000, 65, .9 union
select 3, 2, 'A1', 8000, 60, .8
select * from #data
select D0.propertyId, D0.frontage,
D1.useCode, D1.price,
D2.useCode, D2.price,
D3.useCode, D3.price,
D4.useCode, D4.price,
D5.useCode, D5.price,
D6.useCode, D6.price
from (select propertyId, sum(frontage) as frontage
from #data group by propertyId) as D0
left outer join #data D1 on D0.propertyId = D1.propertyId and D1.segmentNum = 1
left outer join #data D2 on D0.propertyId = D2.propertyId and D2.segmentNum = 2
left outer join #data D3 on D0.propertyId = D3.propertyId and D3.segmentNum = 3
left outer join #data D4 on D0.propertyId = D4.propertyId and D4.segmentNum = 4
left outer join #data D5 on D0.propertyId = D5.propertyId and D5.segmentNum = 5
left outer join #data D6 on D0.propertyId = D6.propertyId and D6.segmentNum = 6
May 29, 2008 at 8:24 am
This looks more promising and more efficient than what I had originally set up. And I was getting stuck on the pivot table idea. So, let me get to work on this and see what I can do.
Thanks for the ideas!
..Gwen..
May 29, 2008 at 8:43 am
The CTE was a pretty good Idea - you could try this (this is the older version of a pivot table):
;WITH Land_CTE (LandLineNumber,
UseCode,
UnitPrice,
FrontFeet,
Property_ID,
Land_ID) AS
(SELECT ROW_NUMBER() OVER (PARTITION BY P.Property_ID ORDER BY L.Land_ID),
L.Usecode,
L.UnitPrice,
L.Frontage,
P.Property_ID,
L.Land_ID
FROM Property P
JOIN Property2Land P2L ON P2L.Property_ID=P.Property_ID
JOIN Land L ON L.Land_ID = P2L.Land_ID
)
SELECT PropertyID,
SUM(FrontFeet) AS FRONT_FT,
Max(case when LandLineNumber=1 then UseCode else null end) as Use_Code_1,
Max(case when LandLineNumber=1 then UnitPrice else null end) as Unit_Price_1,
Max(case when LandLineNumber=2 then UseCode else null end) as Use_Code_2,
Max(case when LandLineNumber=2 then UnitPrice else null end) as Unit_Price_2,
Max(case when LandLineNumber=3 then UseCode else null end) as Use_Code_3,
Max(case when LandLineNumber=3 then UnitPrice else null end) as Unit_Price_3,
Max(case when LandLineNumber=4 then UseCode else null end) as Use_Code_4,
Max(case when LandLineNumber=4 then UnitPrice else null end) as Unit_Price_4,
Max(case when LandLineNumber=5 then UseCode else null end) as Use_Code_5,
Max(case when LandLineNumber=5 then UnitPrice else null end) as Unit_Price_5,
Max(case when LandLineNumber=6 then UseCode else null end) as Use_Code_6,
Max(case when LandLineNumber=6 then UnitPrice else null end) as Unit_Price_6
from LandCTE
group by PropertyID
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 29, 2008 at 9:02 am
If it helps any, the PIVOT operator uses an aggregate function to consolidate information from a given column, based on possible values of a 2nd column. For character data, the only aggregate functions that work are MAX and MIN. You also have to know all the desired values for that 2nd column, and list them with braces. These new fields then have to go into your SELECT clause. These fields then pick up the aggregate value from the 1st column as their values, based on a record in the source data where the 2nd column matches in value to one of the listed values. Unfortunately, PIVOT can only do this for one column, so if multiple values need to come through the process, that's why I convert them to fixed-length character strings and concatenate them together first, and split them apart in a later step. Even if you don't use the technique on this task, it may come in handy later.
Steve
(aka smunson)
:):):)
Gwen (5/29/2008)
This looks more promising and more efficient than what I had originally set up. And I was getting stuck on the pivot table idea. So, let me get to work on this and see what I can do.Thanks for the ideas!
..Gwen..
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 30, 2008 at 12:37 pm
I really do appreciate these ideas. I'm still working on it, though, but right now I'm not even sure what to ask next. :unsure:
Many thanks for the input.
..Gwen..
June 4, 2008 at 2:25 pm
Steve,
By using the aggregate function on the COMBINED_DATA field, I'm
getting multiple rows per property, rather than one row per property.
Using the table #data Antonio posted earlier, the output is like:
Property Use_1 Price_1 Use_2 Price_2 Use_3 Price_3 (etc)
1 A 16000 null null null null
1 null null A1 7000 null null
1 null null null null A1 6000
What I wanted was:
Property Use_1 Price_1 Use_2 Price_2 Use_3 Price_3 (etc)
1 A 16000 A1 7000 A1 7000
And, because there are multiple rows returned, I can't join this to
other tables, because property "cannot be bound."
If I combine 6 unit codes and 6 unit prices in one big COMBINED_DATA
field, that should give me one row per property. Perhaps I can work
out a series of Lefts and Rights to parse the data back out.
The fun continues...
..Gwen..
June 4, 2008 at 2:59 pm
Can you post the T-SQL that provides this result?
Steve
(aka smunson)
:):):)
Gwen (6/4/2008)
Steve,By using the aggregate function on the COMBINED_DATA field, I'm
getting multiple rows per property, rather than one row per property.
Using the table #data Antonio posted earlier, the output is like:
Property Use_1 Price_1 Use_2 Price_2 Use_3 Price_3 (etc)
1 A 16000 null null null null
1 null null A1 7000 null null
1 null null null null A1 6000
What I wanted was:
Property Use_1 Price_1 Use_2 Price_2 Use_3 Price_3 (etc)
1 A 16000 A1 7000 A1 7000
And, because there are multiple rows returned, I can't join this to
other tables, because property "cannot be bound."
If I combine 6 unit codes and 6 unit prices in one big COMBINED_DATA
field, that should give me one row per property. Perhaps I can work
out a series of Lefts and Rights to parse the data back out.
The fun continues...
..Gwen..
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 5, 2008 at 7:08 am
Using the table #data from the earlier post:
with land_cte (PropertyID, Row_Num, UseCode, Price, CombinedData, frontage) as
(select propertyId, segmentNum, useCode, price,
(cast(useCode AS char(4)) + cast(price as char(15))) as CombinedData, frontage from #data)
select propertyid, [1],
CAST(RTRIM(LEFT([1],4)) as varchar(4)) AS USE_CODE_1,
CAST (RTRIM(RIGHT([1],15)) as varchar(15)) AS UNIT_PRICE_1,
[2], [3],[4],[5],[6]
from land_cte
pivot (max(CombinedData) for Row_Num in ([1],[2],[3],[4],[5],[6])) as pvt
Note: This output purposely only shows how [1] would be broken out into usecode and price.
June 5, 2008 at 7:52 am
Gwen -
Your Pivot is doing a grouping by propertyID AND usecode, which is wrecking your "alignment". pull the usecode and price out (leave them in the combined), and I think you'll get the output you want.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 5, 2008 at 7:55 am
The pivot is an all at once kind of thing. It appears you're trying to do only one piece at a time, so take a look at the following code:
DECLARE @LANDDATA TABLE (
PROPERTY_ID int,
SEGMENT_NUM int,
USE_CODE varchar(8),
PRICE money,
FRONTAGE decimal(8,2),
ACREAGE decimal(8,2)
)
INSERT INTO @LANDDATA
SELECT 1, 1, 'A', 16000, 75, 1.25 UNION ALL
SELECT 1, 2, 'A1', 7000, 25, .8 UNION ALL
SELECT 1, 3, 'A1', 6000, 75, 1.25 UNION ALL
SELECT 2, 1, 'B', 3000, 35, .25 UNION ALL
SELECT 2, 2, 'A1', 2000, 35, .25 UNION ALL
SELECT 2, 3, 'A1', 2000, 35, .25 UNION ALL
SELECT 2, 4, 'B2', 2000, 35, .25 UNION ALL
SELECT 2, 5, 'B3', 2000, 35, .25 UNION ALL
SELECT 2, 6, 'B4', 3500, 45, .35 UNION ALL
SELECT 2, 7, 'B5', 3000, 45, .30 UNION ALL
SELECT 3, 1, 'A1', 8000, 65, .9 UNION ALL
SELECT 3, 2, 'A1', 8000, 60, .8;
WITH FRONTAGE_TOTALS AS (
SELECT PROPERTY_ID, SUM(FRONTAGE) AS FRONTAGE_TOTAL
FROM @LANDDATA
GROUP BY PROPERTY_ID
),
LIMIT_IT AS (
SELECT *
FROM @LANDDATA
WHERE SEGMENT_NUM < 7
),
MERGE_IT AS (
SELECT L.PROPERTY_ID, L.SEGMENT_NUM, F.FRONTAGE_TOTAL,
CAST(USE_CODE AS char(20)) + CAST(PRICE AS char(20)) AS COMBINED_DATA
FROM LIMIT_IT AS L LEFT OUTER JOIN FRONTAGE_TOTALS AS F
ON L.PROPERTY_ID=F.PROPERTY_ID
),
PIVOT_IT AS (
SELECT PROPERTY_ID, FRONTAGE_TOTAL, [1], [2], [3], [4], [5], [6]
FROM MERGE_IT
PIVOT (MAX(COMBINED_DATA) FOR SEGMENT_NUM IN ([1],[2],[3],[4],[5],[6])) AS PVT
)
SELECT PROPERTY_ID, FRONTAGE_TOTAL,
CAST(RTRIM(LEFT([1],20)) AS varchar(8)) AS USE_CODE_1,
CAST(RTRIM(RIGHT([1],20)) AS money) AS PRICE_1,
CAST(RTRIM(LEFT([2],20)) AS varchar(8)) AS USE_CODE_2,
CAST(RTRIM(RIGHT([2],20)) AS money) AS PRICE_2,
CAST(RTRIM(LEFT([3],20)) AS varchar(8)) AS USE_CODE_3,
CAST(RTRIM(RIGHT([3],20)) AS money) AS PRICE_3,
CAST(RTRIM(LEFT([4],20)) AS varchar(8)) AS USE_CODE_4,
CAST(RTRIM(RIGHT([4],20)) AS money) AS PRICE_4,
CAST(RTRIM(LEFT([5],20)) AS varchar(8)) AS USE_CODE_5,
CAST(RTRIM(RIGHT([5],20)) AS money) AS PRICE_5,
CAST(RTRIM(LEFT([6],20)) AS varchar(8)) AS USE_CODE_6,
CAST(RTRIM(RIGHT([6],20)) AS money) AS PRICE_6
FROM PIVOT_IT
ORDER BY PROPERTY_ID
I had to use a table variable because I don't have access to temp table capability, but I think you can adapt this code to your CTE.
Steve
(aka smunson)
:):):)
Gwen (6/5/2008)
Using the table #data from the earlier post:
with land_cte (PropertyID, Row_Num, UseCode, Price, CombinedData, frontage) as(select propertyId, segmentNum, useCode, price,
(cast(useCode AS char(4)) + cast(price as char(15))) as CombinedData, frontage from #data)
select propertyid, [1],
CAST(RTRIM(LEFT([1],4)) as varchar(4)) AS USE_CODE_1,
CAST (RTRIM(RIGHT([1],15)) as varchar(15)) AS UNIT_PRICE_1,
[2], [3],[4],[5],[6]
from land_cte
pivot (max(CombinedData) for Row_Num in ([1],[2],[3],[4],[5],[6])) as pvt
Note: This output purposely only shows how [1] would be broken out into usecode and price.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply