SQL CrossTab - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

SQL Server 6.5 \ SQL Server 7.0

SQL CrossTab

Author

Topic

cheatasp
Starting Member

10 Posts

Posted - 2009-05-13 : 22:32:27

Dear,

I have one problem with sql CrossTab, in query I don't use aggregate function because my field contain only string and I want to group it by only one row?.

Otherwise is there any function aggregate function for string?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-13 : 22:33:41

you can still use MAX() on the string column

KH
[spoiler]Time is always against us[/spoiler]

cheatasp
Starting Member

10 Posts

Posted - 2009-05-13 : 22:46:38

Dear,
If use MAX() function I think can't, please see my image below

And I want to group it by only 1 row

here is my code:

SELECT AssetCode,
(Case AssetItemID WHEN 1 THEN AssetDetail END) CPU,
(Case AssetItemID WHEN 2 THEN AssetDetail END) RAM,
(Case AssetItemID WHEN 3 THEN AssetDetail END) HDD,
(Case AssetItemID WHEN 4 THEN AssetDetail END) FDD,
(Case AssetItemID WHEN 18 THEN AssetDetail END) [CD/DVD],
(Case AssetItemID WHEN 9 THEN AssetDetail END) Monitor,
(Case AssetItemID WHEN 10 THEN AssetDetail END) UPS,
(Case AssetItemID WHEN 15 THEN AssetDetail END) Adapter,
(Case AssetItemID WHEN 7 THEN AssetDetail END) Mouse,
(Case AssetItemID WHEN 8 THEN AssetDetail END) Keyboard,
(Case AssetItemID WHEN 20 THEN AssetDetail END) CardReader,
(Case AssetItemID WHEN 13 THEN AssetDetail END) Bluetooth,
(Case AssetItemID WHEN 23 THEN AssetDetail END) InfraRed,
(Case AssetItemID WHEN 6 THEN AssetDetail END) BoardID
FROM TAB_AssetItemDetail
WHERE AssetCode='O1-6C-320-06-04'

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-13 : 23:27:09

[code]
SELECT AssetCode,
MAX(Case AssetItemID WHEN 1 THEN AssetDetail END) CPU,
MAX(Case AssetItemID WHEN 2 THEN AssetDetail END) RAM,
MAX(Case AssetItemID WHEN 3 THEN AssetDetail END) HDD,
MAX(Case AssetItemID WHEN 4 THEN AssetDetail END) FDD,
MAX(Case AssetItemID WHEN 18 THEN AssetDetail END) [CD/DVD],
MAX(Case AssetItemID WHEN 9 THEN AssetDetail END) Monitor,
MAX(Case AssetItemID WHEN 10 THEN AssetDetail END) UPS,
MAX(Case AssetItemID WHEN 15 THEN AssetDetail END) Adapter,
MAX(Case AssetItemID WHEN 7 THEN AssetDetail END) Mouse,
MAX(Case AssetItemID WHEN 8 THEN AssetDetail END) Keyboard,
MAX(Case AssetItemID WHEN 20 THEN AssetDetail END) CardReader,
MAX(Case AssetItemID WHEN 13 THEN AssetDetail END) Bluetooth,
MAX(Case AssetItemID WHEN 23 THEN AssetDetail END) InfraRed,
MAX(Case AssetItemID WHEN 6 THEN AssetDetail END) BoardID
FROM TAB_AssetItemDetail
WHERE AssetCode='O1-6C-320-06-04'
GROUP BY AssetCode
[/code]

KH
[spoiler]Time is always against us[/spoiler]

cheatasp
Starting Member

10 Posts

Posted - 2009-05-13 : 23:34:19

Dear,
thank for your reply, it very nice answer

Thanks

cheatasp
Starting Member

10 Posts

Posted - 2009-05-14 : 00:10:13

Dear, again

When your sql join with other tables it still get the same as my image

here my code:

select A.AssetCode, A.Description, D.TTL as 'Department', L.AssetLocation, T.AssetType,
E.Name, A.OwnerType, A.Remark, A.Status,
MAX(Case AID.AssetItemID WHEN 1 THEN AssetDetail END) CPU,
MAX(Case AID.AssetItemID WHEN 2 THEN AssetDetail END) RAM,
MAX(Case AID.AssetItemID WHEN 3 THEN AssetDetail END) HDD,
MAX(Case AID.AssetItemID WHEN 4 THEN AssetDetail END) FDD,
MAX(Case AID.AssetItemID WHEN 18 THEN AssetDetail END) [CD/DVD],
MAX(Case AID.AssetItemID WHEN 9 THEN AssetDetail END) Monitor,
MAX(Case AID.AssetItemID WHEN 10 THEN AssetDetail END) UPS,
MAX(Case AID.AssetItemID WHEN 15 THEN AssetDetail END) Adapter,
MAX(Case AID.AssetItemID WHEN 7 THEN AssetDetail END) Mouse,
MAX(Case AID.AssetItemID WHEN 8 THEN AssetDetail END) Keyboard,
MAX(Case AID.AssetItemID WHEN 20 THEN AssetDetail END) CardReader,
MAX(Case AID.AssetItemID WHEN 13 THEN AssetDetail END) Bluetooth,
MAX(Case AID.AssetItemID WHEN 23 THEN AssetDetail END) InfraRed,
MAX(Case AID.AssetItemID WHEN 6 THEN AssetDetail END) Board

from TAB_Asset as A
left join TAB_Department as D on A.DepartmentCode=D.DepartmentCode
left join TAB_AssetType as T on A.AssetTypeID=T.AssetTypeID
left join TAB_AssetTypeMain as AM on T.AssetTypeMainID=AM.AssetTypeMainID
left join TAB_AssetLocation as L on A.LocationCode=L.AssetLocationCode
left join TAB_Employee as E on A.Owner = E.EmpID
left join TAB_AssetItemDetail AS AID ON AID.AssetCode = A.AssetCode

WHERE AID.AssetCode='O1-6A-320-07-01'
GROUP BY A.AssetCode,A.Description,D.TTL , L.AssetLocation, T.AssetType,E.Name, A.OwnerType, A.Remark, A.Status, AID.AssetDetail

Please help me.

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-14 : 00:12:51

remove AID.AssetDetail from the GROUP BY

KH
[spoiler]Time is always against us[/spoiler]

cheatasp
Starting Member

10 Posts

Posted - 2009-05-14 : 00:30:07

Dear,

thank for best answer, you are very nice with sql statement,
If possible can you teach me?
my yahoo id: cheatasp@yahoo.com
Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources