jjsather
asked on
SQL Server View that turns table values into columns with 1 row of data
Not sure if SQL Server can do this, but I have a table with 3 fields -- SystemFieldKey, SystemFieldName, and SystemFieldValue. I want a View that take some of the rows and makes one of the values the column name, and another value as the value in that column. (The output is always only 1 row of data.)
Example data for the 3 fields I mentioned above...
1, "NameFirst", "John"
2, "NameLast", "Tyler"
3, "AddressCity", "Dallas"
4, "AddressState", "TX"
5, "AddressZip", "44444"
6, "PhoneMobile", "555-123-4567"
7, "PhoneHome", "555-123-4568"
So I'd like to create a View (vAddresses) that takes all [SystemFieldName] values starting with "Address" and makes each a column (without the "Address" portion) with 1 row containing the matching info in [SystemFieldValue]. I also want it to be dynamic, so if a new row is later added with a [SystemFieldName] starting with "Address", then it will also appear as another column.
So the output in this example (when I'd run "select * from vAddresses") would be...
City, State, Zip (column names)
"Dallas", "TX", "44444" (1 row of data)
Can this be done?
Example data for the 3 fields I mentioned above...
1, "NameFirst", "John"
2, "NameLast", "Tyler"
3, "AddressCity", "Dallas"
4, "AddressState", "TX"
5, "AddressZip", "44444"
6, "PhoneMobile", "555-123-4567"
7, "PhoneHome", "555-123-4568"
So I'd like to create a View (vAddresses) that takes all [SystemFieldName] values starting with "Address" and makes each a column (without the "Address" portion) with 1 row containing the matching info in [SystemFieldValue]. I also want it to be dynamic, so if a new row is later added with a [SystemFieldName] starting with "Address", then it will also appear as another column.
So the output in this example (when I'd run "select * from vAddresses") would be...
City, State, Zip (column names)
"Dallas", "TX", "44444" (1 row of data)
Can this be done?
I guess you mean this?
create table vAddresses
(
ID int,
SystemFieldName varchar(20),
SystemFieldValue varchar(30)
);
insert into vAddresses(ID,SystemFieldName,SystemFieldValue) values (1, 'NameFirst', 'John');
insert into vAddresses(ID,SystemFieldName,SystemFieldValue) values (2, 'NameLast', 'Tyler');
insert into vAddresses(ID,SystemFieldName,SystemFieldValue) values (3, 'AddressCity', 'Dallas');
insert into vAddresses(ID,SystemFieldName,SystemFieldValue) values (4, 'AddressState', 'TX');
insert into vAddresses(ID,SystemFieldName,SystemFieldValue) values (5, 'AddressZip', '44444');
insert into vAddresses(ID,SystemFieldName,SystemFieldValue) values (6, 'PhoneMobile', '555-123-4567');
insert into vAddresses(ID,SystemFieldName,SystemFieldValue) values (7, 'PhoneHome', '555-123-4568');
Select
(select SystemFieldValue from vAddresses where SystemFieldName = 'AddressCity') City,
(select SystemFieldValue from vAddresses where SystemFieldName = 'AddressState') State,
(select SystemFieldValue from vAddresses where SystemFieldName = 'AddressZip') Zip,
(select SystemFieldValue from vAddresses where SystemFieldName = 'NameFirst') NameFirst,
(select SystemFieldValue from vAddresses where SystemFieldName = 'NameLast') NameLast,
(select SystemFieldValue from vAddresses where SystemFieldName = 'PhoneMobile') PhoneMobile,
(select SystemFieldValue from vAddresses where SystemFieldName = 'PhoneHome') PhoneHome
Reading the question again it cannot be implemented as the view. If you need to generate dynamic output having variable number of columns depending on the SystemFieldName then you will need Stored procedure for this purpose.
Also the requirement "The output is always only 1 row of data" does not make sense in the (obvious) case of many addresses in the table. You have to either provide ONE value of the SystemFieldKey to produce one row or accept more rows on output.
The Stored procedure must generate the SQL command dynamically (it will be same as the SQL command in my first answer) and then return appropriate number of columns.
So as the question requirement is the view the answer is "No, this not possible to do as the view on SQL Server". If you can accept Stored procedure then let us know.
Please remember the Stored procedure must read the data twice and generate the dynamic SQL command which will slow the speed down significantly. To have fixed number of columns on output will always be faster. You may update the procedure when new column requirement appear.
You should also tell how to solve duplicate entries (e.g. two AddressZIP values for one SystemFieldKey). The MAX() function will suppress them but the output is not necessarily correct in such case. (If you have unique index on SystemFieldKey + SystemFieldName then there cannot be duplicates.)
Possible SP with fixed number of columns concept (update the table name abd SystemFieldKey data type):
Also the requirement "The output is always only 1 row of data" does not make sense in the (obvious) case of many addresses in the table. You have to either provide ONE value of the SystemFieldKey to produce one row or accept more rows on output.
The Stored procedure must generate the SQL command dynamically (it will be same as the SQL command in my first answer) and then return appropriate number of columns.
So as the question requirement is the view the answer is "No, this not possible to do as the view on SQL Server". If you can accept Stored procedure then let us know.
Please remember the Stored procedure must read the data twice and generate the dynamic SQL command which will slow the speed down significantly. To have fixed number of columns on output will always be faster. You may update the procedure when new column requirement appear.
You should also tell how to solve duplicate entries (e.g. two AddressZIP values for one SystemFieldKey). The MAX() function will suppress them but the output is not necessarily correct in such case. (If you have unique index on SystemFieldKey + SystemFieldName then there cannot be duplicates.)
Possible SP with fixed number of columns concept (update the table name abd SystemFieldKey data type):
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE dbo.spAddresses (@AddrKey VARCHAR(20))
AS
SET NOCOUNT ON
SELECT MAX(CASE WHEN SystemFieldName = 'AddressCity' THEN SystemFieldValue ELSE null END) AS [City],
MAX(CASE WHEN SystemFieldName = 'AddressState' THEN SystemFieldValue ELSE null END) AS [State],
MAX(CASE WHEN SystemFieldName = 'AddressZIP' THEN SystemFieldValue ELSE null END) AS [ZIP],
MAX(CASE WHEN SystemFieldName = 'AddressStreet' THEN SystemFieldValue ELSE null END) AS [Street]
FROM <YourTable>
WHERE SystemFieldKey = @AddrKey
How do I know that these rows are related:
1, "NameFirst", "John"
2, "NameLast", "Tyler"
3, "AddressCity", "Dallas"
4, "AddressState", "TX"
5, "AddressZip", "44444"
6, "PhoneMobile", "555-123-4567"
7, "PhoneHome", "555-123-4568"
What if you have this:
1, "NameFirst", "John"
2, "NameLast", "Tyler"
3, "AddressCity", "Dallas"
4, "AddressState", "TX"
5, "AddressZip", "44444"
6, "PhoneMobile", "555-123-4567"
7, "PhoneHome", "555-123-4568"
8, "NameFirst", "Jane"
9, "NameLast", "Doe"
10, "AddressCity", "Buffalo"
11, "AddressState", "NY"
12, "AddressZip", "55555"
13, "PhoneMobile", "555-765-4321"
14, "PhoneHome", "555-865-4321"
How do you know which phone/city/... belongs to what person, or even what first name to what last name?
1, "NameFirst", "John"
2, "NameLast", "Tyler"
3, "AddressCity", "Dallas"
4, "AddressState", "TX"
5, "AddressZip", "44444"
6, "PhoneMobile", "555-123-4567"
7, "PhoneHome", "555-123-4568"
What if you have this:
1, "NameFirst", "John"
2, "NameLast", "Tyler"
3, "AddressCity", "Dallas"
4, "AddressState", "TX"
5, "AddressZip", "44444"
6, "PhoneMobile", "555-123-4567"
7, "PhoneHome", "555-123-4568"
8, "NameFirst", "Jane"
9, "NameLast", "Doe"
10, "AddressCity", "Buffalo"
11, "AddressState", "NY"
12, "AddressZip", "55555"
13, "PhoneMobile", "555-765-4321"
14, "PhoneHome", "555-865-4321"
How do you know which phone/city/... belongs to what person, or even what first name to what last name?
ASKER
There are no duplicate values in field SystemFieldNames -- each row is a unique system wide field we pre-define. The user (via GUI) provides values we use to populate the matching SystemFieldValue field.
The only time a new row is added is if we want to add a new system wide field. So we know all the names, and I could reference each in the view. Sounds like I'll have to do that. I was simply hoping we wouldn't always have to change a view when we add a new row.
The only time a new row is added is if we want to add a new system wide field. So we know all the names, and I could reference each in the view. Sounds like I'll have to do that. I was simply hoping we wouldn't always have to change a view when we add a new row.
>>How do you know which phone/city/... belongs to what person, or even what first name to what last name?
from your Table design you can't. Frankly speaking this is not a good table design to store the data.
having said that, if your data is sorted according to the sequence, let's say 7 records are using to represent of 1 record, or if when we reading SystemFieldName = "NameFirst", then we assume this is a new record. But again, the sequence of data becomes crucial in this case.
from your Table design you can't. Frankly speaking this is not a good table design to store the data.
having said that, if your data is sorted according to the sequence, let's say 7 records are using to represent of 1 record, or if when we reading SystemFieldName = "NameFirst", then we assume this is a new record. But again, the sequence of data becomes crucial in this case.
The original question says something about "SystemFieldKey" which could be used to distinguish among several addresses. BUT jjsather also says "There are no duplicate values in field SystemFieldNames" so we have to expect just one address in the table and nothing more.
If above written is true then we don't need more info to answer the question. The Stored procedure concept will work when you remove the WHERE part.
To have a view or SP with varying number of columns on output is highly non-standard but the SP is achievable.
If above written is true then we don't need more info to answer the question. The Stored procedure concept will work when you remove the WHERE part.
To have a view or SP with varying number of columns on output is highly non-standard but the SP is achievable.
Is this what you want:
CREATE TABLE systemfields(SystemFieldKey int, SystemFieldName varchar(100), SystemFieldValue varchar(100))
INSERT INTO systemfields
SELECT 1, 'NameFirst', 'John' UNION all
SELECT 2, 'NameLast', 'Tyler' UNION all
SELECT 3, 'AddressCity', 'Dallas' UNION all
SELECT 4, 'AddressState', 'TX' UNION all
SELECT 5, 'AddressZip', '44444' UNION all
SELECT 6, 'PhoneMobile', '555-123-4567' UNION all
SELECT 7, 'PhoneHome', '555-123-4568'
SELECT * FROM systemfields s
GO
CREATE VIEW vwAddress
as
SELECT
(SELECT SystemFieldValue FROM systemfields WHERE SystemFieldName='AddressCity') AS City,
(SELECT SystemFieldValue FROM systemfields WHERE SystemFieldName='AddressState') AS [State],
(SELECT SystemFieldValue FROM systemfields WHERE SystemFieldName='AddressZip') AS Zip
GO
SELECT * from vwAddress
ASKER
That's basically what I am doing -- hard coding the fields name by name on each line. As my original post said, I was hoping to avoid that and make it dynamic, but looks like I can't. Oh well.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
No, has to be a view, but good to know it's impossible.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yeah, that's what I'm doing. I meant in regards to my original post, in making it dynamic.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SELECT MAX(CASE WHEN SystemFieldName = 'AddressCity' THEN SystemFieldValue ELSE null END) AS aCity,
MAX(CASE WHEN SystemFieldName = 'AddressState' THEN SystemFieldValue ELSE null END) AS aState,
MAX(CASE WHEN SystemFieldName = 'AddressZIP' THEN SystemFieldValue ELSE null END) AS aZIP
FROM <YourTable>
WHERE SystemFieldKey = <YourKey>
so the view can be defined this way:
CREATE VIEW MyAddress AS
SELECT MAX(CASE WHEN SystemFieldName = 'AddressCity' THEN SystemFieldValue ELSE null END) AS aCity,
MAX(CASE WHEN SystemFieldName = 'AddressState' THEN SystemFieldValue ELSE null END) AS aState,
MAX(CASE WHEN SystemFieldName = 'AddressZIP' THEN SystemFieldValue ELSE null END) AS aZIP
FROM <YourTable>
GROUP BY SystemFieldKey