如何做到这一点? [英] how to accomplish this?

问题描述

有以下数据

以下格式

CREATE TABLE [dbo].[Sample](
    [CCMPNY] [varchar](2) NOT NULL,
    [CCUST#] [numeric](7, 0) NOT NULL,
    [RDDYSR] [varchar](7) NOT NULL,
    [CBLNAM] [varchar](30) NOT NULL,
    [CCYCLE] [varchar](2) NOT NULL,
    [RDSIZE] [varchar](2) NOT NULL,
    [RDCHGC] [varchar](2) NOT NULL,
    [RDDAY] [numeric](1, 0) NOT NULL,
    [RDROUT] [varchar](4) NOT NULL,
    [RDQTY] [numeric](5, 0) NOT NULL

我怎样才能得到下面的布局

how can I get below layout

用红色标记的列

FREQ: "Count" of Days in "RDDYSR" field
LIFTS: "Lifts" = "RDQTY" times "Freq"
VOLUME: "Volume" = "RDQTY" times "RDSIZE" times "Freq"

Week Days columns: 
MON: If "RDDYSR" includes "M", "RDQTY" times "RDSIZE", else "blank".  
TUE: If "RDDYSR" includes "T", "RDQTY" times "RDSIZE", else "blank", etc.

注意事项

that each "CCUST" generates only one data line unless 
(1) there are multiple "RDCHGC" values (CCUST 43), 
(2) there are multiple "RDSIZE" values (CCUST 10723); or, 
(3) there are multiple "FREQ" values (no example).

感谢您的帮助.

推荐答案

这应该可以简单地为您提供您正在寻找的东西.我不确定你星期天的标识符是什么，所以我用 --

This should give you what you're looking for a little bit simply. I'm not sure what your identifier for Sunday is so I've commented this out with --

select CCUST# CCUST, CBLNAM,RDCHGC, RDQTY, RDSIZE, Len(Replace(RDDYSR, '-', '')) FREQ, 
RDQTY * Len(Replace(RDDYSR, '-', '')) LIFTS, 
RDQTY * Cast(RDSIZE as int) * Len(Replace(RDDYSR, '-', '')) Volume, RDDYSR,
Case when CHARINDEX('M', RDDYSR) > 0 then RDQTY * Cast(RDSIZE as int) end as Mon,
Case when CHARINDEX('T', RDDYSR) > 0 then RDQTY * Cast(RDSIZE as int) end as Tue,
Case when CHARINDEX('W', RDDYSR) > 0 then RDQTY * Cast(RDSIZE as int) end as Wed,
Case when CHARINDEX('H', RDDYSR) > 0 then RDQTY * Cast(RDSIZE as int) end as Thu,
Case when CHARINDEX('F', RDDYSR) > 0 then RDQTY * Cast(RDSIZE as int) end as Fri,
Case when CHARINDEX('S', RDDYSR) > 0 then RDQTY * Cast(RDSIZE as int) end as Sat
--,Case when CHARINDEX(RDDYSR, 'X') > 0 then RDQTY * Cast(RDSIZE as int) end as Sun   --Whatever Sunday identifier is

from Sample

Group by CCUST#, CBLNAM, RDCHGC, RDQTY, RDSIZE, RDDYSR

SQLFiddle:http://www.sqlfiddle.com/#!6/cdb92/1/0

这篇关于如何做到这一点?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！