Convert Multiple rows into one using unique field

Question

Post reply

Convert Multiple rows into one using unique field

Labneh

Old Hand

Points: 329
More actions
July 31, 2009 at 7:37 pm

#93934

Hi all,
I have a table that has multiple rows per unique id (keyName)
KeyName ValueName Value
John.Smith DisplayName John Smith
John.Smith Surname Smith
John.Smith GivenName John
John.Smith Extension 1234
Bob.William DisplayName Bob William
Bob.William Surname William
Bob.William GivenName Bob
Bob.William Extension 9876
I want to have an sql statement to show me something like this (maybe inserting them into different table that has the headers already defined)
DisplayName Surname GivenName Extension
John Smith Smith John 1234
Bob William William Bob 9876
Notice, the Value of the ValueName from table 1 is the name of the field of table 2
is it possible?
Why i'm asking this question is because instead of looping 4 times per record, i will loop once per record.
thanks in advance.
Below is the table structure
USE [DBName]
GO
/****** Object: Table [dbo].[tableName] Script Date: 07/31/2009 21:06:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tableName](
[Path] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[KeyName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ClassPath] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Class] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IsKey] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ValueName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Value] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RegType] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RegTypeCode] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Cntd] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 1

You are going to have to join to your table multiple times - once for each value.

SELECT a.KeyName

,a.Value As DisplayName

,b.Value As SurName

,c.Value As GivenName

,d.Value As Extension

FROM tableName a

INNER JOIN tableName b ON b.KeyName = a.KeyName AND b.ValueName = 'SurName'

INNER JOIN tableName c ON c.KeyName = a.KeyName AND c.ValueName = 'GivenName'

INNER JOIN tableName d ON d.KeyName = a.KeyName AND d.ValueName = 'Extension'

WHERE a.ValueName = 'DisplayName'

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Labneh Old Hand Points: 329 More actions · Answer 2

Man, you are THE MAN!!!

Oh my god, that is exactly what i need.

just a question, what if one of the users doesn't have an extension, so there will be no extension row in the first table, when i tried it, the whole record for the user was gone.

is there a way to display nothing in the extension field if the extension row is not in table1?

thanks

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 3

Yes - change the inner joins to outer joins, example:

LEFT JOIN tableName b ON b.KeyName = a.KeyName AND b.ValueName = 'SurName'

LEFT JOIN tableName c ON c.KeyName = a.KeyName AND c.ValueName = 'GivenName'

LEFT JOIN tableName d ON d.KeyName = a.KeyName AND d.ValueName = 'Extension'

Now, the caveat - this will probably not perform very well on a larger table. This table structure is known as OTLT (One True Lookup Table) and has a host of problems. Google the term and you will find hundreds (if not thousands) of articles on why this table structure has problems.

If possible, I would recommend normalizing the table structure.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

If I may suggest, you don't need to join to the same table 4 times to do this. If you had 50 such columns, that would make for some pretty resource intensive code. 😉

First, a bit of a lesson on what this is. It's called an "NVP" table and "NVP" means "Name/Value Pair". It's the little brother of an "EAV" which stands for "Entity, Attribute, Value". They're also called "long skinny tables" by the DBA's who don't really care for them much.

The purpose of such a table is simple... the "column names" are included on each row much like as in an XML row (which is why a lot of DBA's don't like them) which allows a program and users to "add" column names to a "table" on the fly with the obvious disadvantage that two users could "create" two different column names for the same type of data.

The reason why DBA's hate them so much is because of the methods folks use to "pivot" the data to something that makes sense. I have, in fact, seen people self join the same table 50 times to get 50 columns.

One of the better ways to "pivot" such data is known as a "Cross-Tab". SQL Server also has a "Pivot" clause to do it. See the following URL for a discussion on how they both work and why I prefer the Cross-Tab method.

http://www.sqlservercentral.com/articles/T-SQL/63681/

Here's how to do it with a Cross-Tab...

--====== Create and populate a test table.
      -- This is for demo only and is not a part of the solution.
 DECLARE @SomeTable TABLE 
        (KeyName VARCHAR(20),ValueName VARCHAR(20),Value VARCHAR(20))

 INSERT INTO @SomeTable
        (KeyName,ValueName,Value)
 SELECT 'John.Smith','DisplayName','John Smith' UNION ALL
 SELECT 'John.Smith','Surname','Smith' UNION ALL
 SELECT 'John.Smith','GivenName','John' UNION ALL
 SELECT 'John.Smith','Extension','1234' UNION ALL
 SELECT 'Bob.William','DisplayName','Bob William' UNION ALL
 SELECT 'Bob.William','Surname','William' UNION ALL
 SELECT 'Bob.William','GivenName','Bob' UNION ALL
 SELECT 'Bob.William','Extension','9876' UNION ALL
 SELECT 'Sum.Guy'    ,'Surname','Guy' UNION ALL
 SELECT 'Sum.Guy'    ,'GivenName','Sum'

--===== Now, demo the solution.
     -- Flatten the data as required using a "Cross Tab"
 SELECT KeyName,
        DisplayName = MAX(CASE WHEN ValueName = 'DisplayName' THEN Value ELSE '' END),
        Surname     = MAX(CASE WHEN ValueName = 'Surname' THEN Value ELSE '' END),
        GivenName   = MAX(CASE WHEN ValueName = 'GivenName' THEN Value ELSE '' END),
        Extension   = MAX(CASE WHEN ValueName = 'Extension' THEN Value ELSE '' END)
   FROM @SomeTable
  GROUP BY KeyName

Don't let the MAX and GROUP BY spook ya... it works, it's nasty fast for what it does and, as you can see from the demonstration code above, it handles the "missing data" problem quite easily.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

Heh... Jeffrey posted while I was typing my response... I guess this type of table is also known as "OTLT's". Thanks, Jeffrey. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 6

Jeff Moden (7/31/2009)
Heh... and I guess they're also known as "OTLT's". Thanks, Jeffrey. 🙂

It really doesn't matter what name you use - they have a lot of problems. They are useful in certain situations, I have to admit - mostly when using them for configuration settings, application settings, user preferences, etc..., but using this for regular data will eventually cause issues.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

Jeffrey Williams (7/31/2009)
Jeff Moden (7/31/2009)
Heh... and I guess they're also known as "OTLT's". Thanks, Jeffrey. 🙂
It really doesn't matter what name you use - they have a lot of problems. They are useful in certain situations, I have to admit - mostly when using them for configuration settings, application settings, user preferences, etc..., but using this for regular data will eventually cause issues.

I agree... I said as much in my post. We used to joke about how some folks want to put the entire database in one table to make indexing "simple". 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Labneh Old Hand Points: 329 More actions · Answer 8

Jeffry and Jeff, you guys and everyone here are the BEST

THANKS SO MUCH. 🙂

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 9

Thanks for the feedback, Labneh. We aim to please... we sometimes miss but we we're still aiming. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)