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)

Why use cursor in sql server?

1129

---

What is SubQuery in SQL Server 2008

1070

---

What is the name of reporting services config file and what’s it’s used for?

151

---

How to create an multi-statement table-valued function?

1046

---

Tell me what is fill factor?

1007

---

What is database mirroring?

1172

---

Difference between report and query parameter. Why do we need different type of parameter?

1007

---

What can be used instead of trigger?

1205

---

How do I install only the client tools of sql server 2000?

1122

---

Can you always create a cache of a report?

178

---

What is difference between aggregate and analytic function?

975

---

Explain the Ways to improve the performance of a sql azure database?

84

---

What is join query?

938

---

What is a sql join?

1080

---

How to disable a login name in ms sql server?

1169

---