SQLTeam.com | Weblogs | Forums

Convert the Column value to Row in SQL Query using SP

Hi All,

I am working on one requirement where I need to convert the Column value in to Row for one set of record.
My source table is getting populated from json file.


Please help me to achieve this .

Thanks in advance.
Vipin Jha

This should help you

I do not have to perform any aggregation on my data. I given example aggregation has been use

PIVOT requires an aggregate function. Your example would use MAX() or MIN(); they work for any data type, and if you only have one value then they'd both return the same thing.

Hi, Vipin,

If you'll take the time to provide the source data in a readily consumable format (use a CREATE TABLE and provide the data in the form o INSERT/VALUES), I'll show you how with code.

Also, PIVOT kinda sucks for performance and flexibility. What you really want is a CROSSTAB and, again, if you provide the original data in the readily consumable format I just described, I'll show you how.

Hi Sir ,

Below is the create and insert script

create table qa
id int,
qa nvarchar(max)
SELECT 600,'
Q: How was the quality of product?
A: Good quality
Q: Wen you will ruase the question?
A: Today istlef
Q: Hows was your exp?
A: Good

And My Output will be like


please help me to get the code

This is pretty easy to do... especially if you have the SQL Server 2017 Version or better, but can also be done is lesser versions fairly easily, as well.

Which version do you have?

Ok... no response. Here's the 2017+ version. You can get the fnTally function from the following link.

The code below could also be turned into a high-performance iTVF (inline Table Valued Function). The performance of the code could also be enhanced by forcing a binary correlation but let's first see if this is what you expected. And, yes, it will work with more than 1 ID, which is why I also added a Question Number to the output.

 WITH ctePrep AS
(--==== Replace "Q:" and "A:" with Unit Delimiters
        ,QA = REPLACE(REPLACE(qa,N'Q:',NCHAR(31)),N'A:',NCHAR(31))
   FROM dbo.qa
,cteSplit AS
(--==== Split it all up based on the UnitDelimiters
        ,QNum     = (ROW_NUMBER() OVER (ORDER BY t.N)-1)/2+1
        ,IsAnswer = (ROW_NUMBER() OVER (ORDER BY t.N)-1)%2
        ,QASplit  = TRIM(CONCAT(NCHAR(10),NCHAR(13),' ') --Leading/trailing Cr/Lf/Spaces
                    FROM SUBSTRING(QA,t.N+1,(LEAD(t.N,1,30000000) OVER (ORDER BY t.N))-1-t.N))
   FROM ctePrep
  CROSS APPLY dbo.fnTally(1,LEN(QA))t
        ,Question = MAX(IIF(IsAnswer = 0,QASplit,N''))
        ,Answer   = MAX(IIF(IsAnswer = 1,QASplit,N''))
   FROM cteSplit
  WHERE QASplit > N' '
1 Like