How can I create a dynamic table?
I wrote this script long time ago, the option to xml table didnt exist at the time. I recommend another solution such as query to xml. However here is the code
DROP TABLE #payments
DROP TABLE ##mytable
DROP TABLE #paymentHistory
CREATE TABLE #paymentHistory(id INT,amount DECIMAL (9,2),DATE smalldatetime)
INSERT INTO #paymentHistory
VALUES (1,'300','01/01/08')
INSERT INTO #paymentHistory
VALUES (1,'350','01/02/08')
INSERT INTO #paymentHistory
VALUES (1,'100','01/03/08')
INSERT INTO #paymentHistory
VALUES (2,'150','01/01/09')
INSERT INTO #paymentHistory
VALUES (2,'20','01/05/08')
INSERT INTO #paymentHistory
VALUES (2,'1000','02/04/08')
INSERT INTO #paymentHistory
VALUES (2,'50','01/25/09')
INSERT INTO #paymentHistory
VALUES (3,'20','01/01/08')
INSERT INTO #paymentHistory
VALUES (3,'300','01/01/08')
INSERT INTO #paymentHistory
VALUES (4,'78','01/01/09')
INSERT INTO #paymentHistory
VALUES (4,'78','01/01/09')
INSERT INTO #paymentHistory
VALUES (4,'78','01/01/09')
--select * from #paymenthistory
DECLARE @NUMBER TABLE(columna INT)
INSERT INTO @NUMBER
SELECT COUNT(*) FROM #paymenthistory
GROUP BY id
--select * from @number
DECLARE @variable INT
SELECT @variable=MAX(columna) FROM @NUMBER
--print(@variable)
DECLARE @SQL VARCHAR(MAX)
SET @SQL='create table ##mytable
('
while @variable>1 BEGIN
SET @SQL=@SQL+'Payment_amount'+CAST(@variable AS VARCHAR(2))+' varchar(10),
Payment_date'+CAST(@variable AS VARCHAR(2))+' smalldatetime,'
SET @variable=@variable-1
END
SET @SQL=@SQL+'Payment_amount1 varchar(10), payment_date1 smalldatetime)'
EXEC (@SQL)
ALTER TABLE ##mytable
ADD id INT
INSERT INTO ##mytable(id)
SELECT DISTINCT id FROM #paymentHistory
--select * from ##mytable
CREATE TABLE #payments(id INT, amount DECIMAL(9,2), DATE smalldatetime,rownum INT)
INSERT INTO #payments
SELECT id,amount,DATE,
ROW_NUMBER() OVER (partition BY id ORDER BY DATE DESC) AS rownum FROM #paymentHistory
--select * from #payments
--select * from ##mytable
DECLARE @contract INT
DECLARE @pa DECIMAL(9,2)
DECLARE @pd smalldatetime
DECLARE @ROW INT
DECLARE @object VARCHAR(MAX)
DECLARE @object2 VARCHAR(MAX)
while (SELECT COUNT(*) FROM #payments)>0
BEGIN
SELECT top 1 @contract=id,@pa=amount,@pd=DATE,@ROW=rownum FROM #payments
SET @object='update ##mytable set payment_amount'+CAST(@ROW AS VARCHAR(10))+' = ' + CAST(@pa AS VARCHAR(10)) + ' where id='+CAST(@contract AS VARCHAR(20))
EXEC(@object)
SET @object2='update ##mytable set payment_Date'+CAST(@ROW AS VARCHAR(10))+' = ''' + CAST(@pd AS VARCHAR(20)) + ''' where id='+CAST(@contract AS VARCHAR(20))
EXEC(@object2)
DELETE FROM #payments
WHERE id=@contract AND rownum=@ROW
--print (@object)
END
--
SELECT * FROM ##mytable
--select * from #payments