I am working on SQL Server I have sample data like this in a table.
| CustId | Bank | city | rating | date | rnk |
|---|---|---|---|---|---|
| 1 | Deutsche | Delhi | 5 | 10/10/2022 | 1 |
| 1 | BOA | Pune | 6 | 10/10/2022 | 2 |
| 1 | UBS | Mumbai | 7 | 10/10/2022 | 3 |
| 1 | SBI | Chandigarh | 2 | 10/10/2022 | 4 |
| 2 | Axis | Jaipur | 1 | 10/10/2022 | 1 |
| 2 | Amex | Delhi | 3 | 10/10/2022 | 2 |
| 2 | CitiBnk | Kolkatta | 4 | 10/10/2022 | 3 |
My desired output is
| CustId | Bank1 | city1 | rating1 | date1 | Bank2 | city2 | rating2 | date2 | Bank3 | city3 | rating3 | date3 | Bank4 | city4 | rating4 | date4 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Deutsche | Delhi | 5 | 10/10/2022 | BOA | Pune | 6 | 10/10/2022 | UBS | Mumbai | 7 | 10/10/2022 | SBI | Chandigar | 2 | 10/10/2022 |
| 2 | Axis | Jaipur | 1 | 10/10/2022 | Amex | Delhi | 3 | 10/10/2022 | CitiBnk | Kolkata | 4 | 10/10/2022 |
The columns will grow in set of 4, and can grow to hundreds of columns. I have tried pivot and have to repeat this several time. Is there a better solution. Is it possible to get all the pivots step in one "select".
Since the columns are dynamic, I could not get the data into temp table too. But that is not possible with dynamic set of columns .As far as I know you have to first declare the temp table only then can you insert data. Otherwise you have to use a global temporary table, which I think is not a good idea as this database will be used by other sessions too. Please suggest a solution.
Please Review my code. My Attempt, which works but need a better solution:--
IF OBJECT_ID('tempdb.dbo.#tempTbl', 'U') IS NOT NULL
DROP TABLE #tempTbl;
IF OBJECT_ID('tempdb.dbo.#Bank', 'U') IS NOT NULL
DROP TABLE #Bank;
IF OBJECT_ID('tempdb.dbo.#City', 'U') IS NOT NULL
DROP TABLE #City;
IF OBJECT_ID('tempdb.dbo.#Rate', 'U') IS NOT NULL
DROP TABLE #Rate;
IF OBJECT_ID('tempdb.dbo.#DateR', 'U') IS NOT NULL
DROP TABLE #DateR;
IF OBJECT_ID('tempdb.dbo.#DataTbl', 'U') IS NOT NULL
DROP TABLE #DataTbl
CREATE TABLE #tempTbl (CustId int, Bank varchar(10),city varchar(10), rating int, dateR date, rnk int)
INSERT INTO #tempTbl (CustId, Bank,city ,rating,dateR, rnk)
VALUES
(1,'Deutsche','Delhi' ,5 ,'10/10/2022', 1 )
,(1,'BOA','Pune ' ,6 ,'10/10/2022', 2 )
,(1,'UBS','Pune ' ,6 ,'10/10/2022', 3 )
,(1,'SBI','Mumbai' ,7 ,'10/10/2022', 4 )
,(2,'Axis','Jaipur' ,1,'10/10/2022', 1 )
,(2,'Amex','Delhi' ,3 ,'10/10/2022', 2 )
,(2,'CitiBnk','Kolkatta ',4 ,'10/10/2022', 3 )
DECLARE @cols AS NVARCHAR(MAX) =''
SET @cols = STUFF((SELECT distinct ',' +
QUOTENAME(c.rnk)
FROM #tempTbl c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
--================================bank====
DECLARE @colsBnk AS NVARCHAR(MAX)='',
@queryBnk AS NVARCHAR(MAX)='';
SET @colsBnk = STUFF((SELECT distinct ',' +
'['+ CAST(c.rnk AS varchar) + '] AS Bank'+CAST(c.rnk AS varchar)
FROM #tempTbl c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @queryBnk = 'SELECT CustId,'+ @colsBnk +' INTO ##tempBank FROM
( SELECT CustId,Bank,rnk FROM #tempTbl ) as tbl
PIVOT ( MAX(Bank) for rnk IN ('+@cols +') ) as pvt '
-- =============================================city==
DECLARE @colsCity AS NVARCHAR(MAX)='',
@queryCity AS NVARCHAR(MAX)='';
SET @colsCity = STUFF((SELECT distinct ',' +
'['+ CAST(c.rnk AS varchar) + '] AS City'+CAST(c.rnk AS varchar)
FROM #tempTbl c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @queryCity = 'SELECT CustId,'+ @colsCity +' INTO ##tempCity FROM
( SELECT CustId,city,rnk FROM #tempTbl ) as tbl
PIVOT( MAX(city) for rnk IN ('+@cols +') ) as city
'
-- =============================================rating==
DECLARE @colsRate AS NVARCHAR(MAX)='',
@queryRate AS NVARCHAR(MAX)='';
SET @colsRate = STUFF((SELECT distinct ',' +
'['+ CAST(c.rnk AS varchar) + '] AS Rate'+CAST(c.rnk AS varchar)
FROM #tempTbl c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @queryRate = 'SELECT CustId,'+ @colsRate +' INTO ##tempRate FROM
( SELECT CustId,rating,rnk FROM #tempTbl ) as tbl
PIVOT( MAX(rating) for rnk IN ('+@cols +') ) as rate
'
-- =============================================DateR==
DECLARE @colsDateR AS NVARCHAR(MAX)='',
@queryDateR AS NVARCHAR(MAX)='';
SET @colsDateR = STUFF((SELECT distinct ',' +
'['+ CAST(c.rnk AS varchar) + '] AS DateR'+CAST(c.rnk AS varchar)
FROM #tempTbl c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @queryDateR = 'SELECT CustId,'+ @colsDateR +' INTO ##tempDateR FROM
( SELECT CustId,DateR,rnk FROM #tempTbl ) AS tbl
PIVOT( MAX(DateR) for rnk IN ('+@cols +') ) as DateR
'
--========================================================================
/* pushing data into temp table from global temp table as this is a shared db*/
EXECUTE(@queryBnk)
SELECT * INTO #Bank FROM ##tempBank
DROP Table ##tempBank
EXECUTE(@queryCity)
SELECT * INTO #City FROM ##tempCity
DROP Table ##tempCity
EXECUTE(@queryRate)
SELECT * INTO #Rate FROM ##tempRate
DROP Table ##tempRate
EXECUTE(@queryDateR)
SELECT * INTO #DateR FROM ##tempDateR
DROP Table ##tempDateR
SET @cols = STUFF((SELECT distinct ',' +
'Bank' +CAST(c.rnk as varchar)
,', City'++CAST(c.rnk as varchar)
,', DateR'++CAST(c.rnk as varchar)
,', Rate'++CAST(c.rnk as varchar)
FROM #tempTbl c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
DECLARE @query AS NVARCHAR(MAX) =''
--SET @query = 'SELECT '
--SELECT @cols
SET @query = 'SELECT b.CustId, ' + @cols + '
INTO ##DataTbl
FROM #Bank b
LEFT JOIN #City c ON b.CustId = c.CustId
LEFT JOIN #DateR d ON b.CustId = d.CustId
LEFT JOIN #Rate r ON b.CustId = r.CustId'
EXECUTE(@query)
SELECT * INTO #DataTbl FROM ##DataTbl
DROP TABLE ##DataTbl
SELECT * FROM #DataTbl
