December 15, 2010 at 11:19 am
Hello,
I am trying to find a solution to compare columns and values stored in another table with actual table column- values
I have 2 tables
Table1 with following columns and values
ID|Col1|Col2| Col3 |Col4 |Col5| Col6| Col7
--------------------------------------
1 01 a1 c1 02 03 ef a3
Table 2 with following columns and values
ID |Fldname| FldValue | read | Write | delete
-------------------------------------------
1 Col1 01 1 0 0
2 Col1 02 1 1 0
3 Col3 c1 1 1 1
4 Col3 b2 1 0 0
5 Col5 03 1 1 0
I like to get the following result from these 2 tables
Fldvalue | Read | write | delete
----------------------------------------
01 1 0 0
c1 1 1 1
03 1 1 0
Is there a way to do all these with TSQL query inside a sp or function
Below are the scripts to create table and inserting values.
----Create Table1
CREATE TABLE [dbo].[Table1](
[id] [int] NULL,
[col1] [char](10) NULL,
[col2] [char](10) NULL,
[col3] [char](10) NULL,
[col4] [char](10) NULL,
[col5] [char](10) NULL,
[col6] [char](10) NULL,
[col7] [char](10) NULL
) ON [PRIMARY]
------Insert Values
INSERT INTO [Table1]
([id]
,[col1]
,[col2]
,[col3]
,[col4]
,[col5]
,[col6]
,[col7])
VALUES
(1
,'01'
,'a1'
,'c1'
,'02'
,'03'
,'ef'
,'a3')
GO
----Create Table2
CREATE TABLE [dbo].[Table2](
[id] [int] NULL,
[fldname] [char](10) NULL,
[fldvalue] [char](10) NULL,
[read] [int] NULL,
[write] [int] NULL,
[delete] [int] NULL
) ON [PRIMARY]
-----Inserting Values
INSERT INTO [Table2]
([id]
,[fldname]
,[fldvalue]
,[read]
,[write]
,[delete])
VALUES
(1,'col1','01',1,0,0)
GO
INSERT INTO [Table2]
([id]
,[fldname]
,[fldvalue]
,[read]
,[write]
,[delete])
VALUES
(1,'col1','02',1,1,0)
GO
INSERT INTO [Table2]
([id]
,[fldname]
,[fldvalue]
,[read]
,[write]
,[delete])
VALUES
(1,'col3','c1',1,1,1)
GO
INSERT INTO [Table2]
([id]
,[fldname]
,[fldvalue]
,[read]
,[write]
,[delete])
VALUES
(1,'col3','b2',1,0,0)
GO
INSERT INTO [Table2]
([id]
,[fldname]
,[fldvalue]
,[read]
,[write]
,[delete])
VALUES
(1,'col5','03',1,1,0)
GO
Thanks in advance
Kris
December 15, 2010 at 5:01 pm
I am not sure why nobody is posting replies but I found solution below.
declare @cols nvarchar(max), @SQL nvarchar(max), @TableName varchar(10) ='Table1'
select @Cols = stuff((select ', ' + quotename(Column_Name) from INFORMATION_SCHEMA.COLUMNS
where Table_Name = @TableName and Column_Name not like 'ID%' ORDER BY Ordinal_Position
for XML path('')),1,2,'')
set @SQL = ';with cte as (select * from ' + quotename(@TableName)
+ ' UNPIVOT (FldValue for FldName IN (' + @Cols + ')) unpvt)
select T.* from Table2 t INNER JOIN
cte C on T.FldName = C.FldName and T.FldValue = C.FldValue'
execute (@SQL)
December 15, 2010 at 5:55 pm
Sorry for the delay, I was working at my paying job. 😉
This should work for you too if you are running SQL2008.
In production, you should consider indexing Table2 on (fldname,fldvalue).
select t2.fldvalue, t2.[read], t2.write, t2.[delete]
from table1 t1
cross apply (Values
('Col1',col1),
('Col2',col2),
('Col3',col3),
('Col4',col4),
('Col5',col5),
('Col6',col6),
('Col7',col7)
) ca (Col,Val)
join Table2 t2 on t2.fldname = ca.Col and t2.fldvalue = ca.Val
If you are running SQL 2005, you can still use CROSS APPLY but the VALUES clause won't work the way it does in 2008.
Use this instead.
select t2.fldvalue, t2.[read], t2.write, t2.[delete]
from table1 t1
cross apply (
select 'Col1',col1 union all
select 'Col2',col2 union all
select 'Col3',col3 union all
select 'Col4',col4 union all
select 'Col5',col5 union all
select 'Col6',col6 union all
select 'Col7',col7
) ca (Col,Val)
join Table2 t2 on t2.fldname = ca.Col and t2.fldvalue = ca.Val
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
December 15, 2010 at 6:30 pm
Thanks Dixie Flatline ,
I will try this..
My Table1 is dynamic meaning it is table(x) the columns can be different since it may be from another table.
so, I cannot hard code the columns here.
Thanks
Kris
December 16, 2010 at 3:25 pm
Anytime you don't know the tables in advance, you will have to use dynamic SQL to get there. Either way, you may find the Cross Apply easier to code dynamically. But if you already have a working solution that you understand, good job.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply