Declare @UserID nvarchar(256)
Declare @MsgType
int
set @UserID = userID
set @MsgType =
3
CREATE TABLE
#tmpBus3
(
AdminUnitID int,
AdminUnitName nvarchar(256),
CategoryName nvarchar(256),
CategoryID int,
CategoryLevelID int,
ParentID int,
IsMainAdminUnit bit,
PersonID int,
Name nvarchar(256),
UserID nvarchar(256),
level int,
LevelName nvarchar(256)
)
INSERT INTO
#tmpBus3
Exec
[Archv].Cust_GetContactsbyUserID @UserID,@MsgType;
DECLARE
@tmpTable3 AS nvarchar(max);
SELECT
@tmpTable3 = CONCAT(COALESCE(@tmpTable3
+ '] nvarchar(255), [','['),CONCAT('Col', ROW_NUMBER() OVER(ORDER BY
LevelName ASC)))
from
#tmpBus3
set
@tmpTable3='CREATE TABLE #tmpTable3 ( '+@tmpTable3+'] nvarchar(255) )'
DECLARE
@levels AS nvarchar(max);
SELECT @levels = CONCAT(COALESCE(+@levels
+ ''',N''',''),
AdminUnitName)
from
#tmpBus3
set
@levels=''''+@levels+''''
DECLARE
@SqlStatement1 NVARCHAR(MAX)
SET
@SqlStatement1 = N''+@tmpTable3+' insert into #tmpTable3
values ('+@levels+') select * from
#tmpTable3';
print
@SqlStatement1
EXEC(@SqlStatement1)
Drop Table #tmpBus3
The result will be as in the following screenshot
