New twist on Delimited string per ID using many columns…AND this time into a table
OK...this is out there..based on this previous thread that I got a LOT of help on, SQL Team was asked a Question from MikeB to basically take a columns “key“ and marry a set of columns and place them all on 1 row, as a result set, or in to a table.
The correct answer is that this is a presentation issue, but I couldn't help myself. Basically take this:
fkItemID fkCostID Rate Units
-------- -------- --------- ----------
1 21 45.0000 DAY
1 23 400.0000 LSUM
2 22 225.0000 DAY
And Produce this:
fkItemID fkCostID1 Rate1 Units1 fkCostID2 Rate2 Units2
-------- --------- -------- ------ --------- --------- ------
1 21 45.0000 DAY 23 400.0000 LSUM
2 22 225.0000 DAY NULL NULL NULL
Here's the code. Just make sure to add the password for the bcp statements, or make them trusted
USE Northwind
GO
SET NOCOUNT ON
CREATE TABLE mytbEquipmentRental(fkItemID int, fkCostID int, Rate money, Units varchar(10))
GO
INSERT INTO mytbEquipmentRental(fkItemID, fkCostID, Rate, Units)
SELECT 1, 21, 45, 'DAY' UNION ALL
SELECT 1, 23, 400, 'LSUM' UNION ALL
SELECT 2, 22, 225, 'DAY'
GO
SELECT * FROM mytbEquipmentRental
GO
CREATE FUNCTION GetAllOnLine(@id int)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Result VARCHAR(8000)
SELECT @Result = COALESCE(@Result + '|','') + CONVERT(varchar(15),fkCostID)+ '|' +CONVERT(varchar(15),Rate) + '|' + Units
FROM mytbEquipmentRental
WHERE fkItemID=@id
RETURN @Result
END
GO
CREATE VIEW myView99
AS
SELECT CONVERT(varchar(15),fkItemID) + '|' + dbo.GetAllOnLine(fkItemID) AS Data
FROM (SELECT DISTINCT fkItemID FROM mytbEquipmentRental) AS XXX
GO
CREATE VIEW myExport99
AS
SELECT Data+REPLICATE('|',MAX_Delimiters - (LEN(Data)-LEN(REPLACE(DATA,'|','')))) AS ExportData
FROM myView99
CROSS JOIN ( SELECT MAX(LEN(Data)-LEN(REPLACE(DATA,'|',''))) AS MAX_Delimiters
FROM myView99) AS XXX
GO
SELECT * FROM myExport99
GO
DECLARE @cmd varchar(8000)
SELECT @cmd = 'bcp Northwind.dbo.myExport99 out c:\myExport99.txt -c -Usa -P'
EXECUTE master..xp_cmdshell @cmd
GO
DECLARE @MAX_Columns int, @sql varchar(8000), @x int
SELECT TOP 1 @Max_Columns = COUNT(*)
FROM mytbEquipmentRental
GROUP BY fkItemID
ORDER BY 1 DESC
SELECT @sql = 'CREATE TABLE myTable99(fkItemID int', @x = 1
WHILE @x < = @Max_Columns
BEGIN
SELECT @sql = @sql + ', fkCostID' + CONVERT(varchar(3),@x) + ' int'
+ ', Rate' + CONVERT(varchar(3),@x) + ' money'
+ ', Units' + CONVERT(varchar(3),@x) + ' varchar(10)'
SELECT @x = @x + 1
END
SELECT @sql = @sql + ')'
SELECT @sql
EXEC(@sql)
GO
sp_help myTable99
GO
DECLARE @cmd varchar(8000)
SELECT @cmd = 'bcp Northwind.dbo.myTable99 in c:\myExport99.txt -c -t"|" -Usa -P'
EXECUTE master..xp_cmdshell @cmd
GO
SELECT * FROM myTable99
GO
DROP FUNCTION GetAllOnLine
DROP VIEW myExport99
DROP VIEW myView99
DROP TABLE myTable99
DROP TABLE mytbEquipmentRental
GO