How to Check Row against columns data? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2005 Forums

Transact-SQL (2005)

How to Check Row against columns data?

Author

Topic

misstryguy
Starting Member

1 Post

Posted - 2010-12-15 : 17:48:40

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources