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

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

quote:

---

Originally posted by Kristen

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

---

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.

---

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

---

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

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