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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 How to work with matrix data in an SQL Server?
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Constraint Violating Yak Guru

267 Posts

Posted - 06/13/2013 :  16:13:20  Show Profile  Reply with Quote
Hi guys,

I'm provided with a file (excel/csv) that looks like a matrix. The column labels are dates (starting from column 2). Column 1 has names. The cell value = 1 if a person with this name was born on a date specified in the column. Otherwise the value of the cell is 0.

I need to reformat this file and output a CSV with 3 columns as such:

Name, Date, Birthday_Yes_No
Jane, 01/23, No
... etc.

Has anyone ever encountered this kind of problem and can suggest a solution?

Thanks in advance!

James K
Flowing Fount of Yak Knowledge

3873 Posts

Posted - 06/13/2013 :  16:25:49  Show Profile  Reply with Quote
Import the csv file using Import/Export wizard into a table. Import/Export wizard can be invoked from SSMS object explorer, right-click the database name and Tasks -> Import data.

Once you have the database table, use unpivot like shown below; this is only a demo - you will need to adapt it to your data. If you post sample data in consumable format, usually people on the forum will post exact solutions. See here if you need help in generating consumable DDL and sample data
CREATE TABLE #tmp(id INT ,col2 INT,col3 INT ,col4 INT )
INSERT INTO #tmp VALUES (1,0,1,0),(2,1,0,0),(3,0,0,1),(4,0,1,0);

	id, DateCol, DataValue
UNPIVOT  (DataValue FOR DateCol IN ([Col2],[Col3],[Col4]))U
-- WHERE Datavalue = 1

Go to Top of Page

Constraint Violating Yak Guru

267 Posts

Posted - 06/13/2013 :  17:23:55  Show Profile  Reply with Quote
Thank you for helpful information James!
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000