A new SQL Challenge: Create a new table based on three different Tables

Question

question

ImanAlimi asked Jul 12, '16

A new SQL Challenge: Create a new table based on three different Tables

![alt text][1] Hi everybody, I have 3 tables like these: Table 1: Items ItemCode ItemName -------------- ----------------- 1 Item1 2 Item2 3 Item3 Table 2: Properties PropertyCode PropertyName -------------- ----------------- 1 Quantity 2 Weight 3 Price Table 3: Values ItemVCode PropertCode Value ----------------- ------------------ --------------- 1 1 5 (meaning Item1's Quantity is 5) 1 2 1.5 (meaning Item1's Weight is 1.5) 1 3 15 (meaning Item1's Price is 15) 2 1 8 2 2 .85 2 3 22 3 1 1 3 2 5 3 3 28.7 I need to create a table to combine all of them in one view like this: Quantity Weight Price --------------- -------------- ---------------- Item1 5 1.5 15 Item2 8 .85 22 Item3 1 5 28.7 Can anybody help me? I think I need a Stored Procedure for this. Thanks Guys. [1]: /storage/temp/3546-2016-07-12-16-16-28.jpg

sql server table stored procedures

2016-07-12-16-16-28.jpg (93.0 KiB)

5

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

David Wimbush commented · Jul 12, 2016 at 08:38 AM

What have you tried so far?

1 ·

ThomasRushton ♦♦ commented · Jul 12, 2016 at 10:59 AM

Have you read this? https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/

0 ·

sjimmo commented · Jul 12, 2016 at 11:55 AM

Why do you feel a SP is required?

0 ·

ImanAlimi commented · Jul 12, 2016 at 11:57 AM

Is there any other way to do this? I mean do you have any suggestion?

0 ·

ImanAlimi commented · Jul 12, 2016 at 12:00 PM

I have tried creating a temporary table, adding rows and columns using INSERT INTO but it doesnt look right to me. Because if i want to add another property (like SIZE), I NEED to rewrite the whole transaction

0 ·

Answer 1 · 2016-07-12T12:42:58Z

ThomasRushton answered Jul 12, '16

Three things to look at. Dynamic SQL, and PIVOT statements or Cross tab statements. Try googling them together with the name "Jeff Moden", and you come across a useful two-parter on the main SSC site: http://www.sqlservercentral.com/articles/T-SQL/63681/ - lays out the theory and differences between the two approaches (pivot vs cross-tab), and http://www.sqlservercentral.com/articles/Crosstab/65048/ - looks at how to write dynamic crosstab queries. Have fun.

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ImanAlimi commented · Jul 13, 2016 at 01:54 AM

Thanks Thomas, It is exactly what I wanted! :D

0 ·

Answer 2 · 2016-07-13T05:34:04Z

ImanAlimi answered Jul 13, '16

I Used this and it worked well too: SELECT [Name], (SELECT [Value] FROM [Test1].[dbo].[Item-Prop-Value] WHERE [Test1].[dbo].[Item-Prop-Value].[ItemVCode]=[Test1].[dbo].[Items].[VCode] AND [PropertyVCode]=1) AS Week1, (SELECT [Value] FROM [Test1].[dbo].[Item-Prop-Value] WHERE [Test1].[dbo].[Item-Prop-Value].[ItemVCode]=[Test1].[dbo].[Items].[VCode] AND [PropertyVCode]=2) AS Week2, (SELECT [Value] FROM [Test1].[dbo].[Item-Prop-Value] WHERE [Test1].[dbo].[Item-Prop-Value].[ItemVCode]=[Test1].[dbo].[Items].[VCode] AND [PropertyVCode]=3) AS Week3 FROM [Test1].[dbo].[Items] ORDER BY [Name] The beauty of it is it works with strings too. the pivot only workd with numbers, I assume.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question