sql for building dynamic columns and rows from 2 tables.

Question

sql for building dynamic columns and rows from 2 tables.

npranj

SSCrazy

Points: 2323
More actions
April 18, 2011 at 1:56 am

#389308

Table 1:
SortSeqID ColumnHeader
1 ‘ColA,ColB,ColC’
2 ‘ColA,ColB,ColC,ColD’
3 ‘ColA,ColC,ColD’
Table 2:
SortSeqIDColumnData
1 ‘1,2,3’
2 ’11,13,243,5’
3 ‘1,5,6’
Need output like:
SortSeqIDColAColBColCColD
1123
211132435
3156
Currently we are building dynamic query in order to achieve this however it is taking more than 20 secs to execute. need some sql so that we can bypass the dynamic query.
Pl see current code below which returns results but causes a great performance hit.
select @FinalSql =
coalesce(@FinalSql + ' ' , '') +
' INSERT INTO #TempTab ( SortSequenceInt, ' + TableColumns.[Columns] + ')'
+ ' Select ' + CONVERT(VARCHAR(10), TableData.SortSequenceInt) + ' ,' + TableData.[Data]
FROM
(SELECT DISTINCT SortSequenceInt, (STUFF((SELECT ',' + '''' + ltrim(ColumnValueTxt) + ''''
FROM @AllData B
WHERE A.SortSequenceInt = B.SortSequenceInt
ORDER BY B.AuditColumnMasterId FOR XML PATH('')), 1, 1, '')) as [Data]
FROM @AllData A) TableData
INNER JOIN
(SELECT DISTINCT SortSequenceInt, (STUFF((SELECT ',' + '[' + ltrim(A.AuditColumnNm) + ']'
FROM @AllData AC
INNER JOIN @AllColumns A on AC.AuditColumnMasterId = A.AuditColumnMasterId
WHERE AC.SortSequenceInt = AD.SortSequenceInt
ORDER BY AC.AuditColumnMasterId
FOR XML PATH('')), 1, 1, '')) as [Columns]
FROM @AllData AD) TableColumns on TableColumns.SortSequenceInt = TableData.SortSequenceInt

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 1

That data structure will continue to burn you throughout your professional career at whatever company you're working for. Make them change it to a normalized data structure. Don't wait... do it now.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

npranj SSCrazy Points: 2323 More actions · Answer 2

Thanks Jeff.. these are not the actual tables. We are getting two resultsets and want a solution thereafter. However, i do get your point. Cant help much at the moment w.r.t data structure since it is a legacy app. 🙁