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?
jjsatherAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pcelbaCommented:
You may use following query:
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
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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

Open in new window

pcelbaCommented:
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):
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

Open in new window

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

ZberteocCommented:
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?
jjsatherAuthor Commented:
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.
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
>>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.
pcelbaCommented:
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.
ZberteocCommented:
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 

Open in new window

jjsatherAuthor Commented:
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.
pcelbaCommented:
You cannot implement as a view.

Is Stored procedure OK?
jjsatherAuthor Commented:
No, has to be a view, but good to know it's impossible.
pcelbaCommented:
The view is possible but you have to predefine all the future columns. They will contain NULL values but it could be OK (maybe).
jjsatherAuthor Commented:
Yeah, that's what I'm doing. I meant in regards to my original post, in making it dynamic.
pcelbaCommented:
BTW, I've found a way how to create the view "dynamically"...

The idea is to check/redefine the view after a new row appears in your table. This you can achieve in a Trigger which could be based on this one:
ALTER TRIGGER dbo.test1_ins 
   ON  dbo.test1 
   AFTER INSERT
AS 
BEGIN
	SET NOCOUNT ON;

  -- here you may check the view definition against the current table contents and decide whether to alter the existing view or not...
  -- Store the ALTER VIEW column into a variable
  DECLARE @viewDef nvarchar(4000)
  SET @viewDef = N'ALTER VIEW vTest AS SELECT C1, C2 FROM dbo.Test1'

    EXEC sp_executesql @viewDef

END
GO

Open in new window

Of course, the ALTER VIEW statement will be much more complex in your case and you should build it based on your table contents and execute it when there is a new column added into the view...

Remember important fact: Above data processing and the view update will slow the processing down significantly.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ZberteocCommented:
That is an example of how NOT to implement something: have a trigger modify a view using dynamic query at every insert! . :)
pcelbaCommented:
Of course, I agree but please provide better solution for given data and requirements... :-)

What else to do if this is the only billable solution? All other solutions were rejected and it is hard to convince customers about the truth.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.