Row-to-column Conversion

-- Dynamic Processing

Select a. stuname, A. bzktypename, cast (A. bkcode as varbinary (max) even,
Row_number () over (partition by stuname, bzktypename order by getdate () ID
Into # T1
From bklist
-- Where stuname = 'lin Jianhui'
 
Declare @ sql1 varchar (max)
Declare @ sql2 varchar (max)

Declare @ ID int
Declare @ maxid int
Select @ maxid = max (ID)
From # T1

Set @ ID = 1
Set @ sql1 =''
Set @ sql2 =''
While @ ID <@ maxid
Begin

Set @ sql1 = @ sql1 + '[' + Cast (@ ID as varchar (10) + '],'
Set @ sql2 = @ sql2 + 'case when' + '[' + Cast (@ ID as varchar (10) + ']'
+ 'Is null then''' else cast ('+' ['+ Cast (@ ID as varchar (10) +']'
+ 'As varchar (100) + '', ''end +'
Set @ ID = @ ID + 1
End
Set @ sql1 = @ sql1 + '[' + Cast (@ ID as varchar (10) + ']'

Set @ sql2 = @ sql2 + 'case when' + '[' + Cast (@ ID as varchar (10) + ']'
+ 'Is null then''' else cast ('+' ['+ Cast (@ ID as varchar (10) +']'
+ 'As varchar (100) end'
 
-- Print @ sql1
-- Print @ sql2
 
 
 

Declare @ SQL varchar (max)
Set @ SQL ='
Select stuname, bzktypename, left (bkcode, Len (bkcode)-1) from (
Select stuname, bzktypename,
'+ @ Sql2 +'
Bkcode
From (select * from # T1
) S2 evaluate (max (even) for ID in ('+ @ sql1 +') as PVT
 
) S3

'
Print @ SQL
Exec (@ SQL)

 

-------------------------------------------------------------------

-- Executed statement

Select stuname, bzktypename, left (bkcode, Len (bkcode)-1) from (
Select stuname, bzktypename,
Case when [1] is null then ''else cast ([1] As varchar (100) + ', 'end
+ Case when [2] is null then ''else cast ([2] As varchar (100) + ', 'end
+ Case when [3] is null then ''else cast ([3] As varchar (100) + ', 'end
+ Case when [4] is null then ''else cast ([4] As varchar (100) + ', 'end
+ Case when [5] is null then ''else cast ([5] As varchar (100) End
Bkcode
From (select * from # T1
) S2 round (max (even) for ID in ([1], [2], [3], [4], [5]) as PVT
 
) S3

 

 

 

 

 

Bytes ----------------------------------------------------------------------------------------------------------

-- A Comparison with the XML Method

 

-- XML script

Select B. stuname, B. bzktypename, left (NT, Len (NT)-1) as evennew
From (
Select stuname, bzktypename ,(
Select bkcode + ','
From bklist
Where stuname = A. stuname
And bzktypename = A. bzktypename
Order by stuname, bzktypename
For
XML Path ('')
) As NT
From bklist
Group by stuname, bzktypename
) B