Creating tables - 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

General SQL Server Forums

New to SQL Server Programming

Creating tables

Author

Topic

khiko
Starting Member

1 Post

Posted - 2009-03-12 : 00:46:57

Hi Guys,

I am not really sure how to do this..

I created a table in my database from a csv with the following format

Group Member Company Date << column header
Group1 Member1 Company1 11/03/2009
Group2 Member1 Company1 11/03/2009
Group1 Member2 Company1 12/03/2009
Group3 Member2 Company1 12/03/2009

and i want to have an output format wherein

Member Date Group1 Group2 Group3
Member1 11/03/2009 X X
Member2 12/03/2009 X X

as you can see all duplicate entries are merge into 1 row and an X mark is put under the group where the member belongs to.

God hope im making sense. Any help will be very much appreciated.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-12 : 01:00:11

use pivot or dynamic cross tab
go through this link once
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-12 : 13:00:24

[code]
SELECT Member, Date,
MAX(CASE WHEN Group ='Group1' THEN 'X' ELSE NULL END) AS Group1, MAX(CASE WHEN Group ='Group2' THEN 'X' ELSE NULL END) AS Group2,
MAX(CASE WHEN Group ='Group3' THEN 'X' ELSE NULL END) AS Group3
FROM Table
GROUP BY Member, Date
[/code]

if groups are not known in advance, you can extend this as in last link

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources