SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Cross Tab?
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

MikeB
Constraint Violating Yak Guru

Canada
387 Posts

Posted - 02/23/2005 :  11:10:35  Show Profile
How can I get a result set in the form of a cross tab without knowing the column names?


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


Notice that for fkItemID (1) there are 2 entries but for fkItemID (2) there is only 1 entry. When I select records for a certain item
I would like it to be in cross tab format:


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   


Is there any easy way to do this?

Mike B

robvolk
Most Valuable Yak

USA
15657 Posts

Posted - 02/23/2005 :  13:56:46  Show Profile  Visit robvolk's Homepage
Take a look here:

http://www.sqlteam.com/item.asp?ItemID=2955

But it won't really work for what you're describing, because you have a variable number of columns per group in the result set. This can't be done in SQL, you might be able to do something like it in a report though.
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 02/23/2005 :  14:08:30  Show Profile
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
Go to Top of Page

MikeB
Constraint Violating Yak Guru

Canada
387 Posts

Posted - 02/23/2005 :  14:21:01  Show Profile
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!

Mike B
Go to Top of Page

MikeB
Constraint Violating Yak Guru

Canada
387 Posts

Posted - 02/23/2005 :  14:23:03  Show Profile
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


Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 02/23/2005 :  14:43:57  Show Profile
>> 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
Go to Top of Page

MikeB
Constraint Violating Yak Guru

Canada
387 Posts

Posted - 02/23/2005 :  15:05:27  Show Profile
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


Well, that is a little harder to explain and I won't go into it here because it mostly revolves around c++ application which is off topic!

Mike B
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 02/23/2005 :  15:11:47  Show Profile
quote:
Originally posted by robvolk

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



Can't be done?


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



Brett

8-)
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 02/23/2005 :  15:42:00  Show Profile
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
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 02/23/2005 :  15:44:04  Show Profile
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



I imagine you'd need the appropriate peripherals



Brett

8-)
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000