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.
| 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 |
|
|
|
|
|
|
|
|
SQL CrossTab