Columns to rows query help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Columns to rows query help

Author

Topic

learntsql

524 Posts

Posted - 2012-02-02 : 00:38:18

Hi All,

I have a table with almost 70 columns and each column represents one different property.
table contains millions of records.

Now my requirement is i have to write a query where each row display PropertyName and PropertyValue
for eg:
output like

Name -- Value
P1 -- 1000
P2 -- 20.2
.
.
.

etc...

Please guide me what is the best way to write to handle some millions of records.
TIA.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-02-02 : 01:10:59

Have you considered an EAV design for this? Your output fits that exactly.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

learntsql

524 Posts

Posted - 2012-02-02 : 01:16:56

Thanks Tara,

But not sure what is EAV design.
Never worked before on this.
Please guide me some more detailed way.
TIA.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-02-02 : 01:51:39

This table would turn into 3 tables, your main columns in the first table, list of attributes in another table, and in the third you'd have your linking data for the main table and the attribute table.

Here's an example, a dumb example (have a headache and can't think clearly, lol):

Person - PersonId, Name, Email
Attribute - AttributeId, AttributeName
Person_Attribute - AttributeId, PersonId, Value

Attribute would have optional things like hair color. And then Person_Attribute would link the person to each of their attributes. So if a person had 30 extra attributes besides what's in the main table, then you'd have 30 rows in Person_Attribute for that PersonId.

Hope that makes sense, and sorry for the stupid example. Heading to bed to get rid of this headache.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

learntsql

524 Posts

Posted - 2012-02-02 : 02:01:38

Thanks Tara,

I have to work on this as its new to me.
If anyone worked on this kind of requirements please guide me.
Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-02 : 10:24:33

quote:
Originally posted by learntsql

Thanks Tara,

I have to work on this as its new to me.
If anyone worked on this kind of requirements please guide me.
Thanks.

I thought Tara's explanation was clear enough
Whats the part in which you want guidance on?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources