sql - Combining Database Entries Using a Key to Produce Table with One Entry per Key -

sql - Combining Database Entries Using a Key to Produce Table with One Entry per Key -

what have

i have sql database several tables interrelated integer keys. here 3 tables, column names, , sample info each table. maintain in mind typing give idea, not direct copy/paste database (and formatting meant convey point, not readable sql database parser.)

table 1

itemtable itemid,itemname, fruitorveggie, color 1, apple, fruit, reddish 2, orange, fruit, orange 3, carrot, vegetable, orange

table 2

attributetypestable attributeid,attributename 1, cost 2, weight 3, diameter

table 3

itemattributestable itemid,attributeid,attributevalue 1, 1, .75 1, 2, .5 1, 3, .7 2, 1, .9 2, 3, .7 3, 1, .3 3, 2, .5

note how have multiple entries each itemid in itemattributestable - part trying consolidate in new table.

what want

from these 3 tables want create new table this.

newtable itemid,itemname,fruitorveggie,color,price,weight,diameter 1, apple, fruit, red, .75, .5, .7 2, orange, fruit, orange, .9, , .7 3, carrot, vegetable, orange, .3, .5,

in newtable, itemid unique key there 1 entry per itemid - goal. note how each attributename column in new table , how corresponding info itemattributestable listed here single entry each itemid (leaving field blank if itemattributestable doesn't have entry attributeid itemid). not want have hard code in column names because actual info has around dozen columns , want query versatile plenty able maintain using if attributename changes, add together or remove of them, etc.

how there

i'm looking @ sql involved sort of complex query, although shell of sort create new table might nice. example, query , python script runs query create itemattributestable.

the key parts how create column in new table based on entry in table (in case, attributename) , how pull info multiple tables populate new table.

in sqlserver2005+ can utilize pivot operator rotating table-valued expression.select…into creates new table , inserts resulting rows query it

if object_id('newtable') not null drop table newtable select itemid, itemname, fruitorveggie, color, price, weight, diameter newtable ( select t.itemid, t.itemname, t.fruitorveggie, color, attributename, attributevalue itemtable t bring together itemattributestable @ on t.itemid = at.itemid bring together attributetypestable tt on at.attributeid = tt.attributeid ) x pivot ( max(attributevalue) attributename in ([price], [weight], [diameter]) ) p select * newtable

demo on sqlfiddle

or

if have unknown number of columns(attributename) transformation, can utilize dynamic pivot.

declare @cols nvarchar(max), @query nvarchar(max) select @cols = stuff((select distinct ',' + quotename(attributename) attributetypestable xml path(''), type).value('.', 'nvarchar(max)'), 1, 1, '') if object_id('newtable') not null drop table newtable set @query = 'select itemid, itemname, fruitorveggie, color, ' + @cols + 'into newtable ( select t.itemid, t.itemname, t.fruitorveggie, color, attributename, attributevalue itemtable t bring together itemattributestable @ on t.itemid = at.itemid bring together attributetypestable tt on at.attributeid = tt.attributeid ) x pivot ( max(attributevalue) attributename in (' + @cols + ') ) p ' exec(@query) select * newtable

demo on sqlfiddle

sql