To Convert Output from Column format to a Row
-
Hi ,
I have a sql query whose output is in following format
Column1 Column2 Address 12 RICHMOND ROAD City ATLANTA State Gabc Zip Code 2345-9087 Now i want to convert the output to the given format without making use of cursor Address City State Zip Code 12 RICHMOND ROAD ATLANTA Gabc 2345-9087 Kindly let me know how to do it.
Vikram
.
-
I get worried when post after post I continue to see tables designed this way.
Take a look at normalizing databases,
and the Case operator
select case column1 when 'Address' then column2 end as 'Address',
case column1 when 'City' then column2 end as 'City',
case column1 when 'State' then column2 end as 'State',
case column1 when 'Zip Code' then column2 end as 'Zip Code'
From Mytable
But you will need something to perform a group by are there any other fields in your table the you will be querying by?
Edit:
Here's a thread that I find interesting and pertains to your table design.
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=223900
-
STOP, LOOK, and LISTEN. This is a terrible way to design a database. Follow the link above and look at my article here:
http://www.sqlservercentral.com/columnists/dpeterson/lookuptablemadness.asp
and look at the responses to this article:
http://www.sqlservercentral.com/columnists/rnarasimhan/databasedesignandreferencetables.asp
This approach will cause nothing but grief down the road.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
Hi guys ...
I agree! That is extremely bad database design. Could you please explain the reasons why your table has been designed that way?
I've been trying to think of some but could not come up with anything.
Cheers
-
Hi Guys,
I had the same problem, but my table design is a little different
DROP TABLE metaTable;
DROP TABLE metaType;
DROP TABLE metaAttribute;
DROP TABLE metaValue;
CREATE TABLE metaTable(
ID INT,
Name VARCHAR(80)
);
INSERT INTO metaTable(ID, Name) VALUES(1, 'Product');
CREATE TABLE metaType(
ID INT,
Name VARCHAR(40)
);
INSERT INTO metaType(ID, Name) VALUES(1, 'STRING');
INSERT INTO metaType(ID, Name) VALUES(1, 'INT');
INSERT INTO metaType(ID, Name) VALUES(1, 'DOUBLE');
CREATE TABLE metaAttribute(
ID INT,
TableID INT,
Name VARCHAR(80),
TypeID INT
);
INSERT INTO metaAttribute(ID, TableID, Name, TypeID) VALUES(1, 1, 'ProductID', 1);
INSERT INTO metaAttribute(ID, TableID, Name, TypeID) VALUES(2, 1, 'Name1' , 1);
INSERT INTO metaAttribute(ID, TableID, Name, TypeID) VALUES(3, 1, 'Name2' , 1);
INSERT INTO metaAttribute(ID, TableID, Name, TypeID) VALUES(4, 1, 'Unit' , 2);
INSERT INTO metaAttribute(ID, TableID, Name, TypeID) VALUES(5, 1, 'Price' , 3);
CREATE TABLE metaValue(
TableID INT,
AttributeID INT,
RowID INT,
Value VARCHAR(255)
);
INSERT INTO metaValue(TableID, AttributeID, RowID, Value) VALUES(1, 1, 1, '1210809');
INSERT INTO metaValue(TableID, AttributeID, RowID, Value) VALUES(1, 2, 1, 'Knifes');
INSERT INTO metaValue(TableID, AttributeID, RowID, Value) VALUES(1, 3, 1, 'black 8cm');
INSERT INTO metaValue(TableID, AttributeID, RowID, Value) VALUES(1, 4, 1, '1');
INSERT INTO metaValue(TableID, AttributeID, RowID, Value) VALUES(1, 5, 1, '3.49');
INSERT INTO metaValue(TableID, AttributeID, RowID, Value) VALUES(1, 1, 2, '4711');
INSERT INTO metaValue(TableID, AttributeID, RowID, Value) VALUES(1, 2, 2, 'Coffee Mugs');
INSERT INTO metaValue(TableID, AttributeID, RowID, Value) VALUES(1, 3, 2, 'red, glass');
INSERT INTO metaValue(TableID, AttributeID, RowID, Value) VALUES(1, 4, 2, '3');
INSERT INTO metaValue(TableID, AttributeID, RowID, Value) VALUES(1, 5, 2, '1.99');
INSERT INTO metaValue(TableID, AttributeID, RowID, Value) VALUES(1, 1, 3, '1233213');
INSERT INTO metaValue(TableID, AttributeID, RowID, Value) VALUES(1, 2, 3, 'Phone');
INSERT INTO metaValue(TableID, AttributeID, RowID, Value) VALUES(1, 3, 3, NULL);
INSERT INTO metaValue(TableID, AttributeID, RowID, Value) VALUES(1, 4, 3, '1');
INSERT INTO metaValue(TableID, AttributeID, RowID, Value) VALUES(1, 5, 3, '12.49');
When you created the tables run the followin statement
SELECT
RowID,
MAX(CASE WHEN mv.AttributeID = 1 THEN mv.Value END) AS ArtikelNr,
MAX(CASE WHEN mv.AttributeID = 2 THEN mv.Value END) AS Name1,
MAX(CASE WHEN mv.AttributeID = 3 THEN mv.Value END) AS Name2,
MAX(CASE WHEN mv.AttributeID = 4 THEN mv.Value END) AS PreisEH,
MAX(CASE WHEN mv.AttributeID = 5 THEN mv.Value END) AS Preis
FROM metaValue mV
WHERE TableID = 1
GROUP BY RowID
This design allows me to be flexible enough to create new columns for my "Product-Table" on the fly. Especially usefull if you have different clients which need different attributes for their products 🙂
My question now is:
I use the MAX()-Function to aggregate the result. Is this a performant way to do so? Or maybe there is a better solution
TIA
Michael
-
What about....
select
RowID,
(
select value
from MetaValue MVI
where MVI.AttributeID = 1 and MVI.tableID = 1 and MVI.RowID = MV.RowID
) as ArtikelNr,
(
select value
from MetaValue MVI
where MVI.AttributeID = 2 and MVI.tableID = 1 and MVI.RowID = MV.RowID
) as Name1,
(
select value
from MetaValue MVI
where MVI.AttributeID = 3 and MVI.tableID = 1 and MVI.RowID = MV.RowID
) as Name2,
(...) as PreisEH,
(...) as Preis
from MetaValue MV
where TableID = 1
Not sure if it will be more or less efficient - depends on the indices you have on your metaValue table. Maybe the groupBy on rowID could be efficient if your clustered index was TableID, RowID, AttributeID in that order. The above query may be easier to understand and avoids the need for a max - which might be useful if you had a datatype that didn't like Max (are there any?).
It should still be said that this is far less efficient than just having the table with the correct structure, but I do understand where you are coming from - I implemented something similar at one stage for a doc management system where I allowed users to create additional columns of different data types as appropriate. I had several values tables, one for each data type (so there was an ints table, a varchar table, etc). Whilst I refrained from altering the tables, I did create a view so that reports, etc could easily retrieve the data and I was comfortable with altering views as it could not upset the underlying data.... But it's still less efficient!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply