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