Interesting Problem - SQL Server Forums

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)

Interesting Problem

Author

Topic

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-20 : 01:49:14

Hi everyone,

I have one table having column names 'column' and 'values'
in that table in 'column' column has Column names of another table named mstCompanyEstablisment and standard value for that column.
Now i need to check whether row value of that table mstCompanyEstablishment is matching with the standard values of the first table or not if not i need true or false accordingly.

If its not sufficient to understand problem then please ask the question.

Vabhav T

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-20 : 01:54:46

why is your system designed this way? this would involve dynamic sql to search within each of columns of table mstCompanyEstablishment for value stored as per the other table.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-20 : 02:22:50

See in the table mstCompanyEstablishment only one row will be there for one id and we are going to fetch only one row by one id
and we have to match that column values with the values of the first standard table for corresponding column name which is stored in table in row format

for ex
in first standard table
data is something like that

ColumnName Value
PFPercent 12
CPFPercent 8.33
PFCeilling 6500

in the table mstcompanyestablishment data is something like that

ID PFPercent CPFPercent PFCeiling
1 12 8.33 6000
2 12.5 8.33 6500

Now we have to check data in mstEstablishment that for each id data is correct with standart or not
say for id one PFCeiling is incorrect as its value in standard table is 6500

Vabhav T

Kristen
Test

22859 Posts

Posted - 2010-02-20 : 02:38:09

We have lookup table like that - where we just want a "friendly description" for Status codes and the like. (We could use a separate table for each column's lookups, but that would bloat our table-count hugely, and such status-code lookup only have 10-or-so distinct values. Not ideal for FKeys though!

Kristen
Test

22859 Posts

Posted - 2010-02-20 : 02:41:16

EDIT: Corrected to swap the tables around

I think this will generate the SQL to make the test:


SELECT 'SELECT ID '
UNION ALL
SELECT	DISTINCT
	', [T_' + ColumnName + '] = CASE WHEN [' + ColumnName + '] IS NULL THEN NULL 
                                         WHEN ' + ColumnName + '_VAL.ColumnName IS NULL THEN ''False'' 
                                         ELSE ''True'' END'
FROM	dbo.StandardTable
UNION ALL
SELECT 'FROM mstcompanyestablishment'
UNION ALL
SELECT	DISTINCT
	'LEFT OUTER JOIN dbo.StandardTable AS ' + ColumnName + '_VAL
	ON ' + ColumnName + '_VAL.ColumnName = ''' + ColumnName + ''''
	+ ' AND ' + ColumnName + '_VAL.Value = ' + ColumnName
FROM	dbo.mstcompanyXXXestablishment

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-20 : 02:57:41

sql 2005 approach
returns id , field info and value that deferred from standard


DECLARE @Lookup table
(ColumnName	varchar(100),
Value Numeric(10,2)
)
INSERT INTO @Lookup
SELECT 'PFPercent',	12 UNION ALL
SELECT 'CPFPercent',	8.33 UNION ALL
SELECT 'PFCeiling',	6500



DECLARE @mstcompanyestablishment table
(ID	int,
PFPercent Numeric(10,2),
CPFPercent Numeric(10,2),
PFCeiling Numeric(10,2)
)
INSERT INTO @mstcompanyestablishment
SELECT 1,	12,	 8.33,	 6000 UNION ALL
SELECT 2,	12.5,	 8.33,	 6500

SELECT m.ID,m.Category,m.Value
FROM (SELECT ID,Category,Value
FROM @mstcompanyestablishment t
UNPIVOT(Value FOR Category IN ([PFPercent],[CPFPercent],[PFCeiling]))u) m
LEFT JOIN @Lookup l
ON l.ColumnName= m.Category
AND l.Value=m.Value
WHERE l.ColumnName IS NULL

output
-----------------------------
ID	Category	Value
1	PFCeiling	6000.00
2	PFPercent	12.50

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2010-02-20 : 03:00:39

Can you do it without knowing the column names before hand though?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-20 : 03:01:48

quote:
Originally posted by Kristen

Can you do it without knowing the column names before hand though?

you can by using dynamic sql

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2010-02-20 : 03:04:03

Seems reasonable, thanks. I assumed there was no "non-dynamic-SQL" method using Pivot (although that would be useful!) thanks for confirming.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-20 : 03:08:04

quote:
Originally posted by Kristen

Seems reasonable, thanks. I assumed there was no "non-dynamic-SQL" method using Pivot (although that would be useful!) thanks for confirming.

You're welcome

You need to determine the columns at runtime so you need to generate the column list and add it dynamically inside unpivot query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-20 : 03:40:12

Visakh's query is solution of my problem...
and yes that is true i will have to generate column list also because that was the sample data in my case i have lot of columns in my tables

thanks visakh...

Vabhav T

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-20 : 03:41:46

But i could nt understand kirsten's query

Vabhav T

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-20 : 03:45:57

quote:
Originally posted by vaibhavktiwari83

Visakh's query is solution of my problem...
and yes that is true i will have to generate column list also because that was the sample data in my case i have lot of columns in my tables

thanks visakh...

Vabhav T

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2010-02-20 : 04:32:35

"But i could nt understand kirsten's query"

Just change the "StandardTable" to your actual table name and run it. Its only SELECT statements, it won't do any harm ...

... otherwise I'm wasting my time trying to help you.

" i will have to generate column list also because that was the sample data in my case i have lot of columns in my tables"

I figured that, which is why I wrote my query to solve that problem for you.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-20 : 05:02:01

Hey kristen dont be angry...

I changed your query with proper table name but its giving error

Msg 207, Level 16, State 1, Line 4
Invalid column name 'ColumnName'.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'ColumnName'.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'ColumnName'.
Msg 207, Level 16, State 1, Line 10
Invalid column name 'ColumnName'.
Msg 207, Level 16, State 1, Line 11
Invalid column name 'ColumnName'.
Msg 207, Level 16, State 1, Line 11
Invalid column name 'ColumnName'.
Msg 207, Level 16, State 1, Line 12
Invalid column name 'ColumnName'.
Msg 207, Level 16, State 1, Line 12
Invalid column name 'ColumnName'.

Vabhav T

Kristen
Test

22859 Posts

Posted - 2010-02-20 : 06:37:11

Sorry, I got your mstcompanyestablishment and StandardTables the wrong way round. I've changed the original code (above) to correct this,.

Here's the code using Visakh's data:


CREATE TABLE #StandardTable
(ColumnName	varchar(100),
Value Numeric(10,2)
)
INSERT INTO #StandardTable
SELECT 'PFPercent',	12 UNION ALL
SELECT 'CPFPercent',	8.33 UNION ALL
SELECT 'PFCeiling',	6500



CREATE TABLE #mstcompanyestablishment
(ID	int,
PFPercent Numeric(10,2),
CPFPercent Numeric(10,2),
PFCeiling Numeric(10,2)
)
INSERT INTO #mstcompanyestablishment
SELECT 1,	12,	 8.33,	 6000 UNION ALL
SELECT 2,	12.5,	 8.33,	 6500 UNION ALL
SELECT 3,	12,	 8.33,	 6500	-- This row is OK


SELECT 'SELECT ID '
UNION ALL
SELECT	DISTINCT
	', [T_' + ColumnName + '] = CASE WHEN [' + ColumnName + '] IS NULL THEN NULL 
                                         WHEN ' + ColumnName + '_VAL.ColumnName IS NULL THEN ''False'' 
                                         ELSE ''True'' END'
FROM	dbo.#StandardTable
UNION ALL
SELECT 'FROM #mstcompanyestablishment'
UNION ALL
SELECT	DISTINCT
	'LEFT OUTER JOIN dbo.#StandardTable AS ' + ColumnName + '_VAL
	ON ' + ColumnName + '_VAL.ColumnName = ''' + ColumnName + ''''
	+ ' AND ' + ColumnName + '_VAL.Value = ' + ColumnName
FROM	dbo.#StandardTable

Generates this code:

SELECT ID 
, [T_CPFPercent] = CASE WHEN [CPFPercent] IS NULL THEN NULL WHEN CPFPercent_VAL.ColumnName IS NULL THEN 'False' ELSE 'True' END
, [T_PFCeiling] = CASE WHEN [PFCeiling] IS NULL THEN NULL WHEN PFCeiling_VAL.ColumnName IS NULL THEN 'False' ELSE 'True' END
, [T_PFPercent] = CASE WHEN [PFPercent] IS NULL THEN NULL WHEN PFPercent_VAL.ColumnName IS NULL THEN 'False' ELSE 'True' END
FROM #mstcompanyestablishment
LEFT OUTER JOIN dbo.#StandardTable AS CPFPercent_VAL
	ON CPFPercent_VAL.ColumnName = 'CPFPercent' AND CPFPercent_VAL.Value = CPFPercent
LEFT OUTER JOIN dbo.#StandardTable AS PFCeiling_VAL
	ON PFCeiling_VAL.ColumnName = 'PFCeiling' AND PFCeiling_VAL.Value = PFCeiling
LEFT OUTER JOIN dbo.#StandardTable AS PFPercent_VAL
	ON PFPercent_VAL.ColumnName = 'PFPercent' AND PFPercent_VAL.Value = PFPercent


which gives these results

ID          T_CPFPercent T_PFCeiling T_PFPercent 
----------- ------------ ----------- ----------- 
1           True         False       True
2           True         True        False

Cleanup:

DROP TABLE #StandardTable
GO
DROP TABLE #mstcompanyestablishment
GO

Kristen
Test

22859 Posts

Posted - 2010-02-20 : 06:41:26

P.S. If you want to only include rows that have at least one FALSE then add this to the initial query:


UNION ALL
SELECT 'WHERE 1=0 '
UNION ALL
SELECT	DISTINCT
	'	OR ([' + ColumnName + '] IS NOT NULL AND ' + ColumnName + '_VAL.ColumnName IS NULL)'
FROM	dbo.StandardTable

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-20 : 06:55:15

Thanks Kirsten

Vabhav T

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources