急求一条sqlServer 的sql语句

num city_id city_name typename
2 510700 绵阳市 普通商标
1 511400 眉山市 普通商标
1 511400 眉山市 中华老字号
1 511400 眉山市 知名商标
我想要的结果是--眉山市:511400 ；普通商标：1，中华老字号：1，知名商标：1

select * from test where city_id=511400 ORDER by typename ，不知道具体是什么条件

select city_name,city_id,typename,count(typename) from cnds a group by city_id,city_name,typename

你题目上的num是指的数量是吗？ 还是说我需要根据你typename下面的类型的个数来判断数量？

1.写死：
select city_name,
city_id,
sum(case typename when '普通商标' then num else 0 end) '普通商标',
sum(case typename when '中华老字号' then num else 0 end) '中华老字号',
sum(case typename when '知名商标' then num else 0 end) '知名商标'
from cnds a group by city_id,city_name;
2.写活
mssqlserver:
select *
from

(
select city_name,
city_id,
typename,
sum(num) num
from cnds a group by city_id,city_name,typename
) as p
pivot ( sum(num) for p.typename in ([普通商标],[中华老字号],[知名商标])) as t;

SELECT DISTINCT a.city_name,a.city_id,b.num AS 普通商标,c.num as 中华老字号,d.num AS 知名商标 from test a
LEFT JOIN
test b
ON
a.city_id=b.city_id AND b.typename='普通商标'
LEFT JOIN
test c
ON
a.city_id=c.city_id AND c.typename='中华老字号'
LEFT JOIN
test d
ON
a.city_id=d.city_id AND d.typename='知名商标'
;

眉山市 511400 1 1 1
绵阳市 510700 2

oracle数据库下可以这样写：

select a.city_name || ':' || a.city_id || ';' ||
listagg(a.typename || ':' || a.num, ',') WITHIN GROUP(ORDER BY a.typename)
from test_csdn a
group by a.city_id, a.city_name;

sqlserver下主要还是行转列的问题。

我用建视图的方法先把商标类型和数量查出来，然后对三个视图联合查询的方式
视图起名就是ces1，ces2，ces3,分别对应普通商标，中华老字号和知名商标
ces1视图如下
SELECT
city_name,
city_id,
typename,
count(*) count
FROM
tablename
WHERE
typename = "普通商标"
GROUP BY
city_id

select m.city_id,
m.city_name,
max(case m.type_name when '普通商标' then m.num else 0 end) as 普通商标,
max(case m.type_name when '中华老字号' then m.num else 0 end) as 中华老字号,
max(case m.type_name when '知名商标' then m.num else 0 end) as 知名商标
from (select t.city_id,t.city_name,t.type_name,sum(num) as num from location t group by t.city_name,t.type_name) m
group by m.city_id
亲测有效，附上思路：先分组求出每个城市的各商标对应数量，再用行转列的形式进行转换