Is This Possible with SQL?

[jddev]

Hi,

I am attempting to get the result below given a table like this. Is this possible? Or, do I need to restructure the data?

ID | Country | Price
1 | Denmark | 80
1 | Belgium | 100
2 | Denmark | 180
3 | Belgium | 50

Result:
ID | Den | Bel
1 | 80 | 100

I must admit that I am baffled on this one. I am still researching this however if you know the answer or could point me in the right direction it would be most appreciated.

Thank you

[jddev]

Here is what my thought is at the moment. Please let me know what you think. Am I getting close, or just way off?

CREATE VIEW [Den] AS
SELECT ID, Price
FROM Table
WHERE Country = "Denmark";

CREATE VIEW [Bel] AS
SELECT ID, Price
FROM Table
WHERE Country = "Belgium";

SELECT ID, [Den].Price AS Den, [Bel].Price AS Bel
FROM Table
LEFT JOIN [Den] ON Table.ID = [Den].ID
LEFT JOIN [Bel] ON Table.ID = [Bel].ID
WHERE Table.ID = 1;

[MyWebsiteAdvise]

Hi,

Everything is possible :)

Are you using MSSQL or MySQL ?

In MSSQL you can do it with SELF Join.

Or you can create additional table with prices and ID only and second table will contain all another data.

Design your tables for easy future use (select, insert and so.) Keep things simple. Any data should not be duplicated, relationship between tables are ID's.

I hope this is helpful for you.

[jddev]

Hi,

This is definitely helpful.

The database is MSSQL. I looked online in response to your post regarding the self join, yet I don't quite see how this would allow you to use the data within a column as column headers. I'm not sure I worded that the best however my original example illustrates my thought. To expand, another result may be:

ID | Den | Bel
1 | 80 | 100
2 | 180 | nil
3 | nil | 50

Would you mind showing me an example of the self join?

Thank you

[MyWebsiteAdvise]

Hi,

For my opinion it will be something like that:

SELECT DISTINCT t1.ID, t2.Price AS Den, t2.Price AS Bel FROM table1 AS t1 INNER JOIN table1 AS t2 ON t1.ID = t2.ID WHERE t1.ID = 1

I am looking for how can you get dynamic column headers.

Or you can get additional table with Country ID, and Country Name and insert Country ID in first table. With this method you will avoid data duplicity.

Moreover, you don't have any unique filed in your table. Which field is primary index ?

[jddev]

Hi,

Thank you again for your help with this.



SELECT DISTINCT t1.ID, t2.Price AS Den, t2.Price AS Bel FROM table1 AS t1 INNER JOIN table1 AS t2 ON t1.ID = t2.ID WHERE t1.ID = 1


Wouldn't this would produce this table?

ID | Den | Bel
1 | 80 | 80
1 | 100 | 100


Or you can get additional table with Country ID, and Country Name and insert Country ID in first table. With this method you will avoid data duplicity.


How would this avoid duplicity, as Country_ID becomes duplicated instead of Country? More importantly though, would your additional table enable the desired result (Perhaps I am missing something...)?

ID | Country_ID | Price
versus
ID | Country | Price


I am looking for how can you get dynamic column headers.

This is what is puzzling me as well! While I am not familiar with Views in SQL, my thought is that they may be needed for this.


Moreover, you don't have any unique filed in your table. Which field is primary index ?

The primary key should be the combination of ID and Country. The two together should be unique for any given record. It might be easier to think of, if I were to have named the fields Country, Zone, and Price.

Many thanks for your thoughts on this.