Tuesday, April 20, 2010

Pivot / UnPivot Example

SQL Query :


DECLARE @Tbl TABLE(Col1 INT, A VARCHAR(1), B VARCHAR(1), C VARCHAR(1) )

INSERT INTO @Tbl
SELECT 1, 'a', 'b', 'c'
UNION
SELECT 2, 'd', 'e', 'f'
UNION
SELECT 3, 'g', 'h', 'i'

SELECT
*
FROM
(
SELECT *
FROM
(
SELECT
Col1 AS Cols
, A AS [A(N1)]
, B AS [B(N2)]
, C AS [C(N3)]
FROM @Tbl
) A
UNPIVOT
( [Value] For [Col1] IN (
[A(N1)]
, [B(N2)]
, [C(N3)]
) ) AS upvt
) B
PIVOT
(
MIN ( [Value])
FOR [Cols]
IN ([1], [2], [3])
) AS pvt;

Input :

----------------
Col1 A B C
----------------
1 a b c
2 d e f
3 g h i
----------------

OutPut :
    
-------------------
Col1 1 2 3
-------------------
A(N1) a d g
B(N2) b e h
C(N3) c f i
-------------------

Friday, April 2, 2010

Search Mutipal Keyword in Multipal Field


BEGIN --Key Words
DECLARE @String as VARCHAR(500)
SET @String = 'Kansas,64108'
END

BEGIN --Sample Data
DECLARE @UserMaster TABLE (UserName VARCHAR(50), Address VARCHAR(50), City VARCHAR(50), Zip VARCHAR(50))
INSERT INTO @UserMaster VALUES ('aa', '1810 Cherry Street', 'Kansas City', '64108')
INSERT INTO @UserMaster VALUES ('bb', '1810 Cherry Street', 'New york City', '64119')
INSERT INTO @UserMaster VALUES ('cc', '1810 Cherry Street', 'Kansas City', '64108')
INSERT INTO @UserMaster VALUES ('dd', '1810 Cherry Street', 'New york City', '64119')
INSERT INTO @UserMaster VALUES ('ee', '1810 Cherry Street', 'Kansas City', '64108')
INSERT INTO @UserMaster VALUES ('ff', '1810 Cherry Street', 'New york City', '64119')
END

BEGIN --Return Result
DECLARE @XML XML
SET @XML ='<Root><SearchKey>' + REPLACE( @String, ',', '</SearchKey><SearchKey>') + '</SearchKey></Root>'

SELECT UserMaster.*, SearchInfo.SearchKey
FROM @UserMaster AS UserMaster
INNER JOIN (
SELECT x.Name.value('.', 'VARCHAR(MAX)') AS SearchKey
FROM @XML.nodes('/Root/SearchKey')x(Name)
) SearchInfo ON UserMaster.City Like '%' + SearchInfo.SearchKey + '%'
OR UserMaster.Zip Like '%' + SearchInfo.SearchKey + '%'
OR UserMaster.Address Like '%' + SearchInfo.SearchKey + '%'
END