October 19, 2016 at 8:04 pm
I'm trying to work out the best way to create this view.
The best way to think of it is to think of the order/order details analogy.
I do a couple of joins to come up with my main columns but now need to join to another column which will contain one or more records that satisfy the inner join.
Ultimately I don't want to be showing the duplicated data from each row returned from the join.
(This view will be for a BI solution and will run overnight so performance is not top of the requirements list. The view will be the source for a SSIS package)
My initial data can be thought of as
ID Name Description
I now need to join against OtherID, Name, ID (FK)
My current return would be
1 'John' 'Some description' 9 'Some Name' 1
1 'John' 'Some description' 10 'Some other Name' 1
1 'John' 'Some description' 11 'Some other other Name' 1
Ultimately I need to return (or at least display)
1 'John' 'Some Description' 9 'Some Name' 10 'Some other Name' 11 'Some other other Name'
As I say, this is the source for a SSIS package so anything that can be done in the transform leg of the journey will be ok too.
This is 2008R2 so any future t-sql enhancements won't be an option
October 20, 2016 at 12:33 am
You mentioned SSIS, so I assume you need to create some delimited file in the end.
If that's correct, then this should work for you:
SELECT Id, name, ColList
FROM msdb.sys.sysobjects o
CROSS APPLY (
SELECT STUFF((
SELECT ',' + CONVERT(VARCHAR(10), c.colID ) + '-' + name
FROM msdb.sys.syscolumns c
WHERE c.id = o.id
ORDER BY colid
FOR XML PATH(''), TYPE
).value('.', 'nvarchar(max)'
), 1,1,''
)
) CL (ColList)
WHERE name = 'sysjobs'
I used here comma as a delimiter.
You you have to use another one - place it instead of comma in CROSS APPLY query.
_____________
Code for TallyGenerator
October 20, 2016 at 12:44 am
Hi, no the denormalised result set will go into a SQL table that will feed BI "bits"
October 20, 2016 at 1:14 am
Jay@Work (10/20/2016)
Hi, no the denormalised result set will go into a SQL table that will feed BI "bits"
How many columns in that denormalised table?
_____________
Code for TallyGenerator
October 20, 2016 at 5:04 pm
Not sure yet, this is a work in progress. Given the nature of the data the number of columns returned from the source would be variable as would the values.
This may help explain it better.
The data revolves around building work.
The main columns will be Owner, Address, Work Type, Completion Date.
The extra data (where the one to many comes in) is around trades people who worked on the building work
So 1 row may look like
John, 123 some place, new roof, carpenter, Steve, Tiler, Jim
Another may be
Fred, 99 Other Place, Build Garage, Carpenter, Jill, Electrician, Jack, Painter, Bill, Drain Layer, Andrew
So the number of trade columns is variable could be 1 set (trade type/name) could be 20.
In the source database these records are joined by a many to many table.
TradeType, WorkID, TradesPersonID
I'm wondering if I am overcomplicating things by trying to denormalise this - perhaps it would be better to duplicate the structure in the destination database?
Work
TradePeople
Work/TradePeople.
I have yet to design the destination DB or the SSIS queries to populate it
October 20, 2016 at 6:13 pm
Jay@Work (10/20/2016)
Not sure yet, this is a work in progress. Given the nature of the data the number of columns returned from the source would be variable as would the values.This may help explain it better.
The data revolves around building work.
The main columns will be Owner, Address, Work Type, Completion Date.
The extra data (where the one to many comes in) is around trades people who worked on the building work
So 1 row may look like
John, 123 some place, new roof, carpenter, Steve, Tiler, Jim
Another may be
Fred, 99 Other Place, Build Garage, Carpenter, Jill, Electrician, Jack, Painter, Bill, Drain Layer, Andrew
So the number of trade columns is variable could be 1 set (trade type/name) could be 20.
In the source database these records are joined by a many to many table.
TradeType, WorkID, TradesPersonID
I'm wondering if I am overcomplicating things by trying to denormalise this - perhaps it would be better to duplicate the structure in the destination database?
Work
TradePeople
Work/TradePeople.
I have yet to design the destination DB or the SSIS queries to populate it
I see you're already starting to realise that you're idea is not doable.
Try to decide of further details - not only number of columns, but their names, data types, try to write a query to find out who was doing roofing job for a site - it will help you to ditch this approach for good.
_____________
Code for TallyGenerator
October 20, 2016 at 6:28 pm
I'm sure it is doable e.g. create a table data type which contains names and values and loop through the values trade=roofer, name=John
I'm just starting to think it may not be worth while though worthwhile though.
Think I'll just SSIS the relevant values from all the 3 tables and put them into the destination table for reporting against
October 20, 2016 at 7:56 pm
Jay@Work (10/20/2016)
I'm sure it is doable e.g. create a table data type which contains names and values and loop through the values trade=roofer, name=JohnI'm just starting to think it may not be worth while though worthwhile though.
Think I'll just SSIS the relevant values from all the 3 tables and put them into the destination table for reporting against
Once again - try to write a CREATE TABLE statement for that "destination table for reporting".
And SELECT statement you intend to use for reporting.
Just to confirm it's doable.
_____________
Code for TallyGenerator
October 20, 2016 at 8:05 pm
I'm going to stick with the simplest option and this is to reconstruct the same table structure in the destination as in the source.
I recently had a similar challenge where I had to create a way to store (and select) an unknown number of rows with variable column names.
This was because the web app had a repeater control where the user could click to add another set of (repeated) questions.
We had no way of knowing if we were going to insert 1 row or 10 and what the row headers would be.
Not keen to go back down that road for a while yet 🙂
October 21, 2016 at 12:21 pm
If you must have all in one row, you can opt for the names of the people on the project to be stored in a XML type column.
----------------------------------------------------
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply