February 12, 2009 at 11:53 am
SQL Server 2005
9.0.3282
I have a table that is in the form of an Excel matrix. The first column has a set of values that I need, and the rest of the column names have the other data set that I need. The rows inbetween contain "X"s if the two values intersect. A crude example below:
NULL ColVal1 ColVal2 ColVal3
ARG X NULL NULL
BRG NULL X X
CRG X NULL X
Where ColVal1...n are the name of the columns. I need to be able to pull out the cases where there is an X. Using the example above, my dataset would be something like:
ARG, ColVal1
BRG, ColVal2
BRG, ColVal3
CRG, ColVal1
CRG, ColVal3
Does anybody have anythoughts on the best way to do this? If necessary, I can have the column names as the first row in the table so I'm dealing with actual row data instead of information_schema values for the column names. Thoughts?
February 12, 2009 at 1:28 pm
Hi,
I guess it depends on how many columns you have as to whether this is a suitable solution... But the code would be easy to copy and paste anyway
create table # (header varchar(128), colval1 varchar(2), colval2 varchar(2), colval3 varchar(2))
insert #
select 'ARG', 'X',NULL, NULL
union all select 'BRG',NULL, 'X', 'X'
union all select 'CRG', 'X', NULL, 'X'
select header, 'colVal1' as col from # where colVal1='X'
union all select header, 'colVal2' as col from # where colVal2='X'
union all select header, 'colVal3' as col from # where colVal3='X'
Another idea I had was maybe create some dynamic SQL to copy your required data into another structure? Let me know if the first option works.
B
February 12, 2009 at 2:16 pm
In my solution I've used the following table schema, although using different column names will not make any difference.
CREATE TABLE dbo.Matrix (
RKey varchar(10) NOT NULL,
ColVal1 char(1) NULL,
ColVal2 char(1) NULL,
ColVal3 char(1) NULL
)
GO
INSERT INTO dbo.Matrix
SELECT 'ARG', 'X', NULL, NULL
UNION ALL SELECT 'BRG', NULL, 'X', 'X'
UNION ALL SELECT 'CRG', 'X', NULL, 'X'
This specific problem can be solved using the following query that uses the UNPIVOT operator:
SELECT U.Row, U.Col FROM (
SELECT Row = [RKey], [ColVal1], [ColVal2], [ColVal3]
FROM dbo.Matrix
WHERE NOT ([RKey] IS NULL)
) P
UNPIVOT (
X FOR Col IN ([ColVal1], [ColVal2], [ColVal3])
) AS U
Here are the results using the above data
[font="Courier New"]Row Col
---------- ----------
ARG ColVal1
BRG ColVal2
BRG ColVal3
CRG ColVal1
CRG ColVal3[/font]
The following script uses the INFORMATION_SCHEMA.COLUMNS view to generate dynamic SQL for a more general solution that allows for a variable number of columns and does not rely on particular column names.
DECLARE @TableName nvarchar(128)
SELECT @TableName = 'Matrix'
DECLARE @RKey nvarchar(128)
SELECT @RKey = QUOTENAME(COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE (TABLE_NAME = @TableName)
AND (TABLE_SCHEMA = 'dbo')
AND (ORDINAL_POSITION = 1)
DECLARE @CKeys nvarchar(max)
SELECT @CKeys = COALESCE(@CKeys + N',', N'') + QUOTENAME(COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE (TABLE_NAME = @TableName)
AND (TABLE_SCHEMA = 'dbo')
AND (ORDINAL_POSITION > 1)
DECLARE @sql nvarchar(max)
SELECT @sql = N'SELECT U.Row, U.Col FROM (
SELECT Row = ' + @RKey + N',' + @CKeys + N' FROM dbo.' + @TableName + N'
WHERE NOT (' + @RKey + N' IS NULL)) P
UNPIVOT (X FOR Col IN (' + @CKeys + N')) AS U'
SELECT @sql
EXEC sp_executesql @sql
February 12, 2009 at 2:27 pm
Thanks for the reply!
In hacking at the problem some more after posting I found another way to do it. The UNPIVOT operator worked like a charm! I used something to the effect of:
create table #My_Temp
(
activity_type varchar(10),
my_col varchar(255)
)
insert into #My_Temp
select
b.activity_type_id
, b.my_col
--, b.xyz
from My_Source a
UNPIVOT(
xyz
FOR FUND IN(
'columnheader1', 'columnheader2',...'columnN'
) as this_table
I know it's pseudocode (required), but the UNPIVOT operator did the trick for me. I haven't tested this for a large scale of data (I'm dealing with <500), but it worked. The one piece I couldn't figure out was the purpose of the 'xyz'. I put it there just because *something* was required, but admittedly haven't done enough research yet to find out it's true purpose. I'll do that in my "free" time.
Thanks!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply