SQLTeam.com | Weblogs | Forums

Convert the Column value to Row in SQL Query using SP

Transact-SQL
#1

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.

image

Please help me to achieve this .

Thanks in advance.
Regards,
Vipin Jha

Convert the string to multiple column
#2

This should help you

#3

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

#4

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.

#5

@vipin_jha123
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.

#6

Hi Sir ,

Below is the create and insert script

create table qa
(
id int,
qa nvarchar(max)
)
INSERT INTO qa
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

image

please help me to get the code

#7

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?

#8

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
 SELECT  ID
        ,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
 SELECT  ID
        ,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
  WHERE SUBSTRING(QA,t.N,1) = NCHAR(31)
)
 SELECT  ID
        ,QNum
        ,Question = MAX(IIF(IsAnswer = 0,QASplit,N''))
        ,Answer   = MAX(IIF(IsAnswer = 1,QASplit,N''))
   FROM cteSplit
  WHERE QASplit > N' '
  GROUP BY ID,QNum
;
1 Like