Answer Posted / jk.garg25

you can write this query in table valued function

DECLARE @RangeValue As VARCHAR(MAX)
SET @RangeValue = '1,2,'

DECLARE @Qry As VARCHAR(MAX)

DECLARE @ResData AS VARCHAR(MAX)
DECLARE @InData AS VARCHAR(MAX)

DECLARE @rId AS INT
DECLARE @Pos AS INT

SET @ResData = ''
SET @InData = ''
WHILE(LEN(@RangeValue)>1)
BEGIN
SET @Pos = CHARINDEX(',',@RangeValue,1)
SET @rId = SUBSTRING(@RangeValue,1,@Pos-1)
SET @ResData = @ResData + CASE WHEN @ResData <> '' THEN ','
ELSE '' END + 'ISNULL(['+ CAST(@rId AS VARCHAR(5)) + '],0)
AS Range' + CAST(@rId AS VARCHAR(5))
SET @InData = @InData + CASE WHEN @InData <> '' THEN ','
ELSE '' END + '['+ CAST(@rId AS VARCHAR(5)) + ']'
SET @RangeValue=SUBSTRING(@RangeValue,@Pos+1,LEN(@RangeValue))
END

SET @Qry =
'SELECT '
+ @ResData +
' FROM
(
SELECT Col1 FROM table1(NoLock)
) p
PIVOT
(
MAX (Col1) FOR Col1 IN (' + @InData + ')
) AS pvt'

--PRINT (@Qry)
EXEC (@Qry)

How to provide column names in insert statements in ms sql server?

1147

---

How to call a function from a stored procedure in SQL Server ?

1021

---

How many database files are there in sql server 2000?what are they?

1048

---

what changed between the previous version of sql server and the current version? : Sql server database administration

998

---

What is open database communication (odbc)?

1056

---

what is an extended stored procedure? Can you instantiate a com object by using t-sql? : Sql server database administration

1016

---

What are the requirements for sql server 2016?

880

---

If a stored procedure is taking a table data type, how it looks?

6437

---

Why olap is used?

963

---

What is use of except clause?

1092

---

Which operator do you use to return all of the rows from one query except rows are returned in a second query?

987

---

How to find the version of sql server? : sql server database administration

1104

---

What is truncate table?

933

---

When should you use an instead of trigger?

950

---

You want to use bids to deploy a report to a different server than the one you chose in the report wizard. How can you change the server url?

155

---