x002548's Blog

Not Just a Number - Brett Kaiser
posts - 89, comments - 586, trackbacks - 39

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

Print | posted on Wednesday, February 23, 2005 3:30 PM | Filed Under [ SQL Server ]

Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET