Source Data
Query
DECLARE @SQL VARCHAR(500)
SELECT @SQL = COALESCE(@SQL + ‘, ‘, ”) + EMPNAME FROM #TMP_EMP
PRINT @SQL
Output
Archive for February, 2012
Combining Multiple Rows into One Row
Posted in T-SQL on February 21, 2012| Leave a Comment »
DECLARE @SQL VARCHAR(500)
Find the Second Highest Salary
Posted in T-SQL on February 21, 2012| 1 Comment »
Find the second highest salary for each department.
Sample Data
Query
SELECT EMPID,EMPName,EMPDept,EMPSal FROM
(
SELECT DENSE_RANK() OVER(partition by EMPDept ORDER BY EMPSAL DESC)AS RowId, *
FROM [dbo].#tmp_EMP
) AS e1
WHERE e1.RowId = 2
(
SELECT DENSE_RANK() OVER(partition by EMPDept ORDER BY EMPSAL DESC)AS RowId, *
FROM [dbo].#tmp_EMP
) AS e1
WHERE e1.RowId = 2
Output
T-SQL Comma Separated Input String.
Posted in T-SQL on February 12, 2012| 2 Comments »
This post about T-SQL comma separated input string.
For Instance.
For Instance.
Input String = ‘SQLServer,SSIS,SSRS,SSAS,BigData’
Expected OutPut
Query
DECLARE @InputString VARCHAR(100)
DECLARE @output NVARCHAR(200)
DECLARE @output NVARCHAR(200)
SET @InputString = ‘SQLServer,SSIS,SSRS,SSAS,BigData’
SET @output = ‘select ”’+REPLACE(@InputString,’,’,”’ UNION ALL SELECT ”’)+””
EXEC(@output)
SQLAssist 