tbEquipmentRental
fkItemID | fkCostID | Rate | Units
---------------------------------------------
1        | 21       | 45   | DAY
1        | 23       | 400  | LSUM
2        | 22       | 225  | DAY

For item id (1)
fkItemID | ID | Rate | Units | ID | Rate | Units
------------------------------------------------
1        | 21 | 45   | DAY   | 23 | 400  | LSUM

For item id (2)
fkItemID | ID | Rate | Units 
-------------------------------
1        | 22 | 225  | DAY   

create table #tbEquipmentRental(fkItemID int, fkCostID int, Rate int, Units char(4))
insert #tbEquipmentRental
select 1,21,45,'DAY' union
select 1,23,400,'LSUM' union
select 2,22,225,'DAY'


declare @fkItemID int
set @fkItemID = 1 --<---------------------------- put value of fkItemID here

declare @sql varchar(8000)
set @sql = 'select	t.fkItemID' + char(10)
select @sql = @sql + '	,t'+ltrim(fkCostID)+'.fkCostID,t'+ltrim(fkCostID)+'.Rate,t'+ltrim(fkCostID)+'.Units' + char(10)
from #tbEquipmentRental where fkItemID = @fkItemID
set @sql = @sql + 'from	(select '+ltrim(@fkItemID)+' as fkItemID) t' + char(10)
select @sql = @sql + '	,#tbEquipmentRental t'+ltrim(fkCostID) + char(10)
from #tbEquipmentRental where fkItemID = @fkItemID
set @sql = @sql + 'where	1 = 1' + char(10)
select @sql = @sql + '	and t'+ltrim(fkCostID)+'.fkItemID = '+ltrim(@fkItemID)+' and t'+ltrim(fkCostID)+'.fkCostID = '+ltrim(fkCostID) + char(10)
from #tbEquipmentRental where fkItemID = @fkItemID

-- select @sql
exec(@sql)

drop table #tbEquipmentRental

quote:

---

Originally posted by rockmoose

This seems like a very odd requirement.
You must use dynamic sql to solve this, here is one solution:

create table #tbEquipmentRental(fkItemID int, fkCostID int, Rate int, Units char(4))
insert #tbEquipmentRental
select 1,21,45,'DAY' union
select 1,23,400,'LSUM' union
select 2,22,225,'DAY'


declare @fkItemID int
set @fkItemID = 1 --<---------------------------- put value of fkItemID here

declare @sql varchar(8000)
set @sql = 'select	t.fkItemID' + char(10)
select @sql = @sql + '	,t'+ltrim(fkCostID)+'.fkCostID,t'+ltrim(fkCostID)+'.Rate,t'+ltrim(fkCostID)+'.Units' + char(10)
from #tbEquipmentRental where fkItemID = @fkItemID
set @sql = @sql + 'from	(select '+ltrim(@fkItemID)+' as fkItemID) t' + char(10)
select @sql = @sql + '	,#tbEquipmentRental t'+ltrim(fkCostID) + char(10)
from #tbEquipmentRental where fkItemID = @fkItemID
set @sql = @sql + 'where	1 = 1' + char(10)
select @sql = @sql + '	and t'+ltrim(fkCostID)+'.fkItemID = '+ltrim(@fkItemID)+' and t'+ltrim(fkCostID)+'.fkCostID = '+ltrim(fkCostID) + char(10)
from #tbEquipmentRental where fkItemID = @fkItemID

-- select @sql
exec(@sql)

drop table #tbEquipmentRental

rockmoose

---

quote:

---

Originally posted by MikeB

quote:

---

Originally posted by rockmoose

This seems like a very odd requirement.
You must use dynamic sql to solve this, here is one solution:

create table #tbEquipmentRental(fkItemID int, fkCostID int, Rate int, Units char(4))
insert #tbEquipmentRental
select 1,21,45,'DAY' union
select 1,23,400,'LSUM' union
select 2,22,225,'DAY'


declare @fkItemID int
set @fkItemID = 1 --<---------------------------- put value of fkItemID here

declare @sql varchar(8000)
set @sql = 'select	t.fkItemID' + char(10)
select @sql = @sql + '	,t'+ltrim(fkCostID)+'.fkCostID,t'+ltrim(fkCostID)+'.Rate,t'+ltrim(fkCostID)+'.Units' + char(10)
from #tbEquipmentRental where fkItemID = @fkItemID
set @sql = @sql + 'from	(select '+ltrim(@fkItemID)+' as fkItemID) t' + char(10)
select @sql = @sql + '	,#tbEquipmentRental t'+ltrim(fkCostID) + char(10)
from #tbEquipmentRental where fkItemID = @fkItemID
set @sql = @sql + 'where	1 = 1' + char(10)
select @sql = @sql + '	and t'+ltrim(fkCostID)+'.fkItemID = '+ltrim(@fkItemID)+' and t'+ltrim(fkCostID)+'.fkCostID = '+ltrim(fkCostID) + char(10)
from #tbEquipmentRental where fkItemID = @fkItemID

-- select @sql
exec(@sql)

drop table #tbEquipmentRental

rockmoose

---

:)
lol, it may be an odd requirment, I am full of them.

The reason this is done is because for some equipment, some cost items are relavent and some aren't. For example, the fkItem 1 is crane rental. The fkCostID 21 = RENTAL and the fkCostID = MOVEIN
where as the fkItem 2 is trailer rental, where fkCostID = RENTAL, there is no "Movein" costs associated with it.

I decided not to do it with the cross tab anyway. What I did was in the stored procedure for the update of a fkCostID for any item, I check to see if the cost item is relevant, if not I return an error message informing the user.

I don't know, sometimes I think this application goes about 4000 steps too far! (the result of a few end user requirments where the end users have imaginations beyond my comprehension!)


Mike B

---

quote:

---

Originally posted by rockmoose

>> I decided not to do it with the cross tab anyway. What I did was in the stored procedure for the update of a fkCostID for any item, I check to see if the cost item is relevant, if not I return an error message informing the user.

Ok, but what does update have to do with crosstab anyway ???
And yes, stored procedures is the way to go.

RAISERROR('This feature is not implemented yet, plz wait for next release',16,1)

rockmoose

---

quote:

---

Originally posted by robvolk

This can't be done in SQL....

---

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


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

tbBev
wd	 | coffeetype   | Amnt |
--------------------------------
1        | BH           | 1    |
2        | CR           | 2    |
3        | BL           | 1    |

quote:

---

Originally posted by rockmoose

Brett,

I have this table:

tbBev
wd	 | coffeetype   | Amnt |
--------------------------------
1        | BH           | 1    |
2        | CR           | 2    |
3        | BL           | 1    |

Can I get sql server to do them for me each morning ?
Or do I hace to GRANT SELECT ON tbev to wife ?

rockmoose

---