x002548's Blog

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