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

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