求sql写法

Question

如图,两张表,我想得出的结果是红色框框的格式,就是类似行转列.

Answer 1

select max(case when itm_no='SAUNDA4T0141BB' THEN itm_no ELSE NULL END ) AS itm_no1,
max(case when itm_no='BKKCMKPUK' THEN itm_no ELSE NULL END ) AS itm_no2
FROM TB

Answer 2

tbl_itm_catalog 得到item关联下表
tbl_sku_catalog 得到颜色..颜色是动态的..

Answer 3

根据itm_no关联sku,将sku拼成一列.select itm_no from tbl_itm_catalog where itm_no in('SAUNDA4T0141BB','MARY  6T5634IL')
MARY  6T5634IL
SAUNDA4T0141BBselect * from tbl_sku_catalog where itm_no in('SAUNDA4T0141BB','MARY  6T5634IL')
MARY  6T5634IL BKL
MARY  6T5634IL TML
SAUNDA4T0141BB BKK
SAUNDA4T0141BB CMK
SAUNDA4T0141BB PUK我想得到的结果是
MARY  6T5634IL BKLTML
SAUNDA4T0141BB BKKCMKPUK

Answer 4

把你的数据和表名改了一下，你研究一下吧----------------------------------------------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2013-03-12 15:50:05
-- Version:
--      Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) 
-- Jun 17 2011 00:54:03 
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1, v.721)
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go 
create table [huang]([itm_no] varchar(14),[itm_col] varchar(3))
insert [huang]
select 'MARY6T5634IL','BKL' union all
select 'MARY6T5634IL','TML' union all
select 'SAUNDA4T0141BB','BKK' union all
select 'SAUNDA4T0141BB','CMK' union all
select 'SAUNDA4T0141BB','PUK'
--------------开始查询--------------------------
SELECT  a.itm_no ,
        STUFF(( SELECT  '' + itm_col
                FROM    [huang] b
                WHERE   b.itm_no = a.itm_no
              FOR
                XML PATH('')
              ), 1, 1, '') 'itm_col'
FROM    [huang] a
GROUP BY a.itm_no----------------结果----------------------------
/* 
itm_no         itm_col
-------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
MARY6T5634IL   KLTML
SAUNDA4T0141BB KKCMKPUK
*/

Answer 5

--创建一个自定义函数用于合并数据
if object_id('F_Str') is not null
    drop function F_Str
go
create function F_Str(@itm_no varchar(30))
returns nvarchar(100)
as
begin
    declare @S nvarchar(100)
    select @S=isnull(@S,'')+itm_col from tbl_sku_catalog where itm_no=@itm_no
    return @S
end
go--输出结果
Select distinct itm_no,itm_col=dbo.F_Str(itm_no) from tbl_sku_catalog/*
itm_no                         itm_col                                                                                              
------------------------------ ---------------------------------------------------------------------------------------------------- 
MARY  6T5634IL                 BKLTML
SAUNDA4T0141BB                 BKKCMKPUK（所影响的行数为 2 行）
*/