Using dynamic sql, how can I rearrange columns

Question

Using dynamic sql, how can I rearrange columns

Viewing 5 posts - 16 through 19 (of 19 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

NineIron - Thursday, March 22, 2018 9:16 AM
Sorry. I copied, pasted and ran your code. This error popped up.
I ran the query inside the CTE like this................
SELECT
col.[name]
--LEFT([col].[name],PATINDEX('%[0-9]%',[col].[name]) - 1),
--CAST(RIGHT([col].[name], LEN([col].[name]) - (PATINDEX('%[0-9]%',[col].[name]) - 1)) AS INT)
FROM [tempdb].[sys].[tables] AS [tab]
INNER JOIN [tempdb].[sys].[columns] AS [col]
ON [col].[object_id] = [tab].[object_id]
WHERE [tab].[name] LIKE '#T%'
AND ([col].[name] LIKE 'diag%' OR [col].[name] LIKE 'poa%')
and got ................
Looks like an extra poa and maybe that's why it choked on coverting something to an int?
name
diag1
diag2
diag3
diag4
diag5
poa1
poa2
poa3
poa4
poa5
poa

Do you have a column named poa with no numeric value appended? The code I wrote based on your original post works on multiple machines with no problem.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Lynn Pettis - Thursday, March 22, 2018 9:20 AM
NineIron - Thursday, March 22, 2018 9:16 AM
Sorry. I copied, pasted and ran your code. This error popped up.
I ran the query inside the CTE like this................
SELECT
col.[name]
--LEFT([col].[name],PATINDEX('%[0-9]%',[col].[name]) - 1),
--CAST(RIGHT([col].[name], LEN([col].[name]) - (PATINDEX('%[0-9]%',[col].[name]) - 1)) AS INT)
FROM [tempdb].[sys].[tables] AS [tab]
INNER JOIN [tempdb].[sys].[columns] AS [col]
ON [col].[object_id] = [tab].[object_id]
WHERE [tab].[name] LIKE '#T%'
AND ([col].[name] LIKE 'diag%' OR [col].[name] LIKE 'poa%')
and got ................
Looks like an extra poa and maybe that's why it choked on coverting something to an int?
name
diag1
diag2
diag3
diag4
diag5
poa1
poa2
poa3
poa4
poa5
poa
Do you have a column named poa with no numeric value appended? The code I wrote based on your original post works on multiple machines with no problem.

Just answered my own question. I added an additional column named poa and got the same error you posted. Your diag and poa column names must have a numeric value appended for the code to work.

NineIron SSChampion Points: 12537 More actions · Answer 3

Each poa and diag has a number at the end. Take a peek at this section of code from the sp. The source data always has included, a DiagnosisUrnID that is concatenated to the diag and poa.

declare @POASql nvarchar(max);

declare @POAColumns nvarchar(max);

set @POAColumns = N'';

;with cteDiagUrn_poa

as

(

select t1.DiagnosisUrnID

from #T1 t1

group by t1.DiagnosisUrnID

)

select @POAColumns += N', p.' + quotename('poa'+convert(varchar(10), cte.DiagnosisUrnID))

from cteDiagUrn_poa cte

order by cte.DiagnosisUrnID;

set @POAColumns = stuff(@POAColumns, 1, 2, '');

NineIron SSChampion Points: 12537 More actions · Answer 4

There is a table called #T1 in sys.tables. How can I drop it from tempdb?

name
#T__________________________________________________________________________________________________________________00000000250F
#T__________________________________________________________________________________________________________________00000000250F
#T__________________________________________________________________________________________________________________00000000250F
#T__________________________________________________________________________________________________________________00000000250F
#T__________________________________________________________________________________________________________________00000000250F
#T__________________________________________________________________________________________________________________00000000250F
#T__________________________________________________________________________________________________________________00000000250F
#T__________________________________________________________________________________________________________________00000000250F
#T__________________________________________________________________________________________________________________00000000250F
#T__________________________________________________________________________________________________________________00000000250F
#T1_________________________________________________________________________________________________________________000000001E45

NineIron SSChampion Points: 12537 More actions · Answer 5

Rather than use #T, I create a table called #Test. I wasn't able to drop the table, #T1.
The code works fine.............which you already knew.
Thanx for the help.