Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
SQL 2008 need to flatten multiple tables into one view

Answered SQL 2008 need to flatten multiple tables into one view

  • Thursday, January 17, 2013 11:53 AM
     
     
    Sign In to Vote
    0

    Hi

    I have a db for a small pharmeceutical company which I am in the process of upscaling from Access. At the moment is is a fairly conventional structure

    e.g. tblProducts lists products, tblIngredients lists ingredients, tblxProdToIng matches product IDs to ingredient IDs and lists the amount of each ingredient per tablet  e.g. productID=205, ingredientID=1456, weight=0.45mg

    In database terms, all fairly sensible.

    However, the first thing I have do do initially in Access and thereafter in SS2008 is to create just one csv file output which is going to be used to populate a printed catalogue. The extension they are using to do this requires the csv to contain the list of products and within each record, all the ingredients. As far as I can see this means I have to take the first ingredient which relates to product 001 and drop it into a field in the view called Ingredient1. Then I have to take the weight of the same ingredient and drop it into IngWeight1, then carry on populating fields until I run out of ingredients. I appreciate that this is a real kludge! It means I have to know how many the maximum number of ingredients in a product is and then design my view to accomodate that plus a bit in case it goes up in future. I will therefore also be outputing lots of blank ingredients records for products with fewer ingredients.

    Because I can only use one csv and the extension for DTP can't handle nested loops, that's the only way I can think of to do it. Any other ideas would be gratefully appreciated. As far as I can see a simple view won't do this, so I need a stored procedure?

    This is getting a bit beyond my expertise, so any help would be much appreciated. 

     

All Replies

  • Thursday, January 17, 2013 3:52 PM
     
     Proposed Answer Has Code
    Sign In to Vote
    0

    so i think that something like this might be in order

    DECLARE @tblProducts TABLE ( id int, name varchar(255) );
DECLARE @tblIngredients TABLE ( id int, productId int, name varchar(255), weightinMg decimal(17,2));
insert into @tblProducts VALUES (1, 'Paracetamol');
insert into @tblIngredients values (1, 1, 'salt', 0.45);
insert into @tblIngredients values (2, 1, 'sugar', 0.05); 

select *, 
	   (
		SELECT CONVERT(VARCHAR, id) + ',' + name + ',' + CONVERT(VARCHAR, weightinMg) + 'mg,' as [data()] FROM @tblIngredients WHERE productId = a.id FOR XML PATH('')
	   ) as Ingredients
from @tblProducts a


    the output for this would be:

    id name Ingredients
    1 Paracetamol 1,salt,0.45mg, 2,sugar,0.05mg,

    with the ingredients being a string containing all of the ingredients. 

    ---
    Shaun Turner

    My Blog | My LinkedIn

    If you're in the UK please join The SQL Developers / DBA's user group for the UK on LinkedIn

     
  • Friday, January 18, 2013 11:29 AM
     
     
    Sign In to Vote
    0

    Hi Shaun

    Thanks for the reply and the idea. It's a good idea, but the problem is that the DTP software won't accept the ingredients data as a string - or rather it will, but it won't format it properly.

    What I need to do is select the largest ingredient by weight for product 1 and put it in its own field IngDesc1, then put its weight into IngWeight1, then find the second ingredient by weight and put that into field IngDesc2 and so on. Every ingredient name and weight has to live in its own field, not be merged into a string. I will have to create more IngDesc and IngWeight fields that I can use, to future proof it.

    Thanks anyway.

     
  • Monday, January 21, 2013 8:03 AM
    Moderator
     
     
    Sign In to Vote
    0

    >put that into field IngDesc2 and so on.

    You should use a JUNCTION table (go vertical not horizontal).

    Example for junction table from AdventureWorks2012.

    Kalman Toth SQL 2008 GRAND SLAM
    Paperback: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

     
  • Monday, January 21, 2013 8:24 AM
     
     
    Sign In to Vote
    0

    Hi Kalman

    Thanks for the reply. Actually I already have a junction table, if I'm understanding you correctly. tblProducts lists products, tblIngredients lists ingredients and tblxProdToIng is the junction table. In database terms you are quite right, the problem is that the extension the DTP people are using to create the printed catalogue isn't very clever. It can only accept a single CSV where everything is horizontal - each record must contain every field.

     
  • Monday, January 21, 2013 8:45 AM
     
      Has Code
    Sign In to Vote
    1

    You can create an SQL View as follows. I'm afraid I don't have experience with Access programming so will not be able to suggest how to create fields dynamically and then populate them with data.

    create table tblProducts
(tblProductsId int,
products varchar(50)
)

create table tblIngredients
(tblIngredientsId int,
ingredients varchar(10))

create table tblxProdToIng
(
tblProductsId int,
tblIngredientsId int,
IngredientWeight decimal(10,3) 
)

insert into tblProducts (tblProductsId, products)values
(1, 'prd1'), (2, 'prd2'), (3, 'prd3')

insert into tblIngredients (tblIngredientsId, ingredients) values
(1, 'ingrd1'), (2, 'ingrd2'),(3, 'ingrd3'),(4, 'ingrd4'),(5, 'ingrd5')


insert into tblxProdToIng(tblProductsId,tblIngredientsId,IngredientWeight)
values 
(1, 1, 1.5),
(1, 2, 2.5),
(2, 3, 1),
(2, 4, 1.5),
(2, 1, 3),
(3, 1, 4)
go

create view vwProductIngredients
as
select p.products, i.ingredients, x.IngredientWeight, ROW_NUMBER() over (partition by p.products order by x.IngredientWeight) as IngredientWeightRankPerProduct
from tblProducts as p
inner join tblxProdToIng as x
on p.tblProductsId = x.tblProductsId
inner join tblIngredients as i
on x.tblIngredientsId = i.tblIngredientsId
go

select * from vwProductIngredients
order by products, IngredientWeightRankPerProduct desc

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.
    Thanks!
    Aalam | Blog (http://aalamrangi.wordpress.com)

     
  • Monday, January 21, 2013 9:51 AM
     
     Answered Has Code
    Sign In to Vote
    0

    Tim,

    I'm trying to understand why this won't work, your output file is a CSV file correct? so in this manner you could do it, the way in which it's formatted within the field can be tweaked easily enough to order the results in any way which you need:

    DECLARE @tblProducts TABLE ( id int, name varchar(255) );
DECLARE @tblIngredients TABLE ( id int, productId int, name varchar(255), weightinMg decimal(17,2));
insert into @tblProducts VALUES (1, 'Paracetamol');
insert into @tblIngredients values (1, 1, 'salt', 0.45);
insert into @tblIngredients values (2, 1, 'sugar', 0.05); 

select *, 
	   (
		SELECT CONVERT(VARCHAR, id) + ',' + name + ',' + CONVERT(VARCHAR, weightinMg) + 'mg,' as [data()] FROM @tblIngredients WHERE productId = a.id ORDER BY weightinMg DESC FOR XML PATH('')
	   ) as Ingredients
from @tblProducts a

    I'm guessing your problem is title fields, which you could union on as the first row a cheat, this would give you a single dynamic view which you could use as a cheat:

    DECLARE @tblProducts TABLE ( id int, name varchar(255) );
DECLARE @tblIngredients TABLE ( id int, productId int, name varchar(255), weightinMg decimal(17,2));
insert into @tblProducts VALUES (1, 'Paracetamol');
insert into @tblIngredients values (1, 1, 'salt', 0.45);
insert into @tblIngredients values (2, 1, 'sugar', 0.05); 
SELECT 1 as [SortOrder], 
	   'ProductId,' + 
	   'ProductName,' +
	   ( 
		SELECT 'IngDesc' + CONVERT(VARCHAR, ROW_NUMBER() OVER ( ORDER BY id )) + ','
			 + 'IngWeight' + CONVERT(VARCHAR, ROW_NUMBER() OVER ( ORDER BY id )) + ',' as [data()]
		FROM @tblIngredients y
		INNER JOIN 
		(
			SELECT TOP 1 productId FROM @tblIngredients GROUP BY productId ORDER BY COUNT(*) DESC
		) z ON y.productId = z.productId
		FOR XML PATH ('')
	  ) as [Content]
UNION ALL
select 2, 
	   convert(varchar, a.id) + ',' + 
	   a.Name + ',' +
	   (
		SELECT name + ',' + CONVERT(VARCHAR, weightinMg) + 'mg,' as [data()] FROM @tblIngredients WHERE productId = a.id ORDER BY weightinMg DESC FOR XML PATH('')
	   ) as Ingredients
from @tblProducts a
ORDER BY 1

    This way everything appears in the content field with the headers and all you've got to do is dump that data from the content field directly to a filestream.

    The only other way that I could think to do this would be using the pivot commands but again this would hard code the number of fields whereas the approach above get's rid of the need to do so:

    DECLARE @tblProducts TABLE ( id int, name varchar(255) );
DECLARE @tblIngredients TABLE ( id int, productId int, name varchar(255), weightinMg decimal(17,2));
insert into @tblProducts VALUES (1, 'Paracetamol');
insert into @tblIngredients values (1, 1, 'salt', 0.45);
insert into @tblIngredients values (2, 1, 'sugar', 0.05); 
insert into @tblProducts VALUES (2, 'Sweets');
insert into @tblIngredients values (3, 2, 'acid', 0.15);
insert into @tblIngredients values (4, 2, 'chloroform', 0.55); 

SELECT c.productid, 
	   c.name, 
	   d1.name as IngDesc1, d1.weightinMg as IngWeight1,
	   d2.name as IngDesc2, d2.weightinMg as IngWeight2,
	   d3.name as IngDesc3, d3.weightinMg as IngWeight3,
	   d4.name as IngDesc4, d4.weightinMg as IngWeight4,
	   d5.name as IngDesc5, d5.weightinMg as IngWeight5
FROM @tblProducts a
inner join
	(
		SELECT productId, id, ROW_NUMBER() OVER (PARTITION BY productID ORDER BY WeightInMg DESC) AS RN
		FROM @tblIngredients
	) b on a.id = b.productid
pivot 
(
	MAX (b.Id) 
	FOR RN IN ( [1], [2], [3], [4], [5])
) c
left join @tblIngredients d1
	on	c.[1] = d1.id
left join @tblIngredients d2
	on	c.[2] = d2.id
left join @tblIngredients d3
	on	c.[3] = d3.id
left join @tblIngredients d4
	on	c.[4] = d4.id
left join @tblIngredients d5
	on	c.[5] = d5.id

    Hope this helps

    ---
    Shaun Turner

    My Blog | My LinkedIn

    If you're in the UK please join The SQL Developers / DBA's user group for the UK on LinkedIn

    • Marked As Answer by TimPoll Thursday, January 24, 2013 2:18 PM
    •  
     
  • Thursday, January 24, 2013 2:18 PM
     
     
    Sign In to Vote
    0

    Hi and thanks for the replies.

    Apologies for the delay in responding and thanking you all - I've been tied up with something else and only just got back on to this. Shaun, your pivot method is just what I need. It's taken me a while to get it working on my database, but it is now doing just what I need. I just have to put up with the fact that the number of ingredients is hard coded - it's not the end of the world.

    Thanks again

     
  • Thursday, January 24, 2013 3:16 PM
     
     
    Sign In to Vote
    0

    Hi Shaun

    Sorry to be a pest: I know your solution (the last one) works, because when I set up tables exactly the way you did it does work. But when I try to replicate it on my real database I get into a terrible muddle. Trouble is both tables in your example have an id field and a name field, and I'm clearly not following your logic properly and getting the two muddled. Would you mind identifying which usages of the fields is which in the above, please?

    Sorry that I'm being so dense!

    Regards