SQL Server Row to Column wise

Question

创建表脚本：

CREATE TABLE #tempClient 
(
     ClientCode int,
     ClientName nvarchar(250),
     BranchType varchar(1), 
     Address nvarchar(250), 
     Mobile Nvarchar(250), 
     HomePh nvarchar(250)
)
INSERT INTO #tmpClient 
VALUES (1001, 'Test Client', 'H', 'Maxis Street Petaling Jaya', '0125566789', '0355234678'),
       (1001, 'Test Client', 'B', 'J street','0168899123', ''),
       (1002, 'SUper Client', 'H', 'Bukit Bintang', '01289898', '03232232'),
       (1002, 'Super Client', 'B', 'Klang Road', '0168892345', ''),
       (1002, 'Super Client', 'B', 'Puteri', '016223344', '035222322')

我有一个要求客户端具有总部和多个分支机构的要求，因此我想动态地将行数据显示到列。我正在尝试使用Pivot，但我无法，请提出任何建议

输出应为：

ClientCode  ClientName  BranchType  Address                       Mobile         HomePh      BAddr1     BMobile1   BHomePh1  BAddr2  BMobile2    BHomePh2
1001        Test Client  H          Maxis Street Petaling Jaya    0125566789     0355234678  J street   0168899123  NULL     NULL     NULL        NULL
1002        SuperClient  H          Bukit Bintang                 01289898       03232232    Klang Road 0168892345  NULL     Puteri   016223344   035222322

Answer 1

我无法完全找到答案，但是在查询下面，我认为可以做到的技巧。您应该在查询中唯一拿走1行_number。

DECLARE @limit INT, 
        @counter INT =1,
        @dynamicSQL NVARCHAR(MAX)
 Set @limit = (Select MAX(countClientcode) FROM (Select COUNT(ClientCode) As countClientcode,ClientCode FROM #tempClient Group By ClientCode)t)
 SET @dynamicSQL = 'Select  ROW_NUMBER() OVER(PARTITION BY t1.ClientCode Order By t1.ClientCode),* FROM #tempClient t1'
WHILE @counter < @limit
BEGIN
    SET @dynamicSQL += ' LEFT JOIN #tempClient t'+CONVERT(NVARCHAR(5),@counter+1)
                        +' ON t'+CONVERT(NVARCHAR(5),@counter)
                            +'.ClientCode = t'+CONVERT(NVARCHAR(5),@counter+1)
                                +'.ClientCode AND t'+CONVERT(NVARCHAR(5),@counter+1)
                                    +'.BranchType = ''B'' AND t'+CONVERT(NVARCHAR(5),@counter)
                                        +'.Address <> t'+CONVERT(NVARCHAR(5),@counter+1)+'.Address'
    SET @counter +=1
END
SET @dynamicSQL += ' WHERE t1.BranchType = ''H'''
EXEC( @dynamicSQL)