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)
 How to work with matrix data in an SQL Server?

Author  Topic 

sql_er
Constraint Violating Yak Guru

267 Posts

Posted - 2013-06-13 : 16:13:20
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-13 : 16:25:49
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 - 2013-06-13 : 17:23:55
Thank you for helpful information James!
Go to Top of Page
   

- Advertisement -