SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

sql_er
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

3653 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 http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
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);

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

DROP TABLE #tmp;
Go to Top of Page

sql_er
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  
 New 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.05 seconds. Powered By: Snitz Forums 2000