Follow by Email

Thursday, July 25, 2013

using cross apply in sql | Example for cross apply in sql | Switiching columns and rows

hi in this post i will show an example on how to use cross apply in sql.

Example :


CREATE TABLE #test (
 ID INT
 ,NAME VARCHAR(10)
 ,salary INT
 )

INSERT INTO #test
SELECT 1
 ,'chandan'
 ,100

SELECT *
FROM #test

SELECT A.COLUMN_NAME [Column_name]
 ,CASE 
  WHEN A.COLUMN_NAME = 'ID'
   THEN Convert(VARCHAR, T.ID)
  WHEN A.COLUMN_NAME = 'Name'
   THEN T.NAME
  WHEN A.COLUMN_NAME = 'Salary'
   THEN Convert(VARCHAR, T.salary)
  END [Value]
FROM tempdb.information_Schema.columns A
CROSS APPLY #test T
WHERE table_name LIKE '%#test%'