Transpose my view - 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

General SQL Server Forums

New to SQL Server Programming

Transpose my view

Author

Topic

Patyk
Yak Posting Veteran

74 Posts

Posted - 2015-02-19 : 13:09:34

Currentely I have a view that looks like this,
SELECT KeyField, FieldName, AlphaValue, FormType
FROM companyB.dbo.AdmFormData
WHERE (FormType = 'STK') AND (FieldName = 'categ') OR
(FormType = 'STK') AND (FieldName = 'flavor') OR
(FormType = 'STK') AND (FieldName = 'type')

My view looks this way,
StockCode FieldName AlphaValue FormType
110100153 categ MOIST STK
110100153 flavor TECH STK
110100153 type SHAM STK
0110100903B categ MOIST STK
0110100903B flavor TECH STK

I would like my New View to Look this way
StockCode categ flavor Type
0110100903B FAST FWD STYLE STYLING
0110100903B FIREWALL SMOOTH STYLING
0110100153 SPRAYFIXX DISCONTINUED STYLING

Any help would be greatly appreciated.

Thanks

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-02-19 : 15:31:45

Please provide:
- description of the source table AdmFormData
- sample data from the source table AdmFormData that would end up as your output:

StockCode   categ     flavor       Type
0110100903B FAST FWD  STYLE        STYLING
0110100903B FIREWALL  SMOOTH       STYLING
0110100153  SPRAYFIXX DISCONTINUED STYLING

Patyk
Yak Posting Veteran

74 Posts

Posted - 2015-02-19 : 15:50:07

Sample of ADMFormData
FormType KeyField FieldName AlphaValue NumericValue DateValue TimeStamp
(stkCode) 0
STK 111701265 categ DRY SHAMPOO 0
STK 111701265 flavor DRY SHAMPOO - SIMPLY 0
STK 111701275 categ DRY SHAMPOO 0
STK 111701275 flavor DRY SHAMPOO - SIMPLY 0
STK 111701275 type STYLING 0
STK 111701295 categ DRY SHAMPOO 0
STK 111701295 flavor DRY SHAMPOO - BLONDE 0
STK 111701295 type STYLING 0
STK 111701305 categ DRY SHAMPOO 0
STK 111701305 flavor DRY SHAMPOO - BLONDE 0

Table Design:
CREATE TABLE [dbo].[AdmFormData](
[FormType] [char](6) NOT NULL,
[KeyField] [char](80) NOT NULL,
[FieldName] [char](6) NOT NULL,
[AlphaValue] [char](100) NULL,
[NumericValue] [decimal](18, 6) NULL,
[DateValue] [datetime] NULL,
[TimeStamp] [timestamp] NULL,
CONSTRAINT [AdmFormDataKey] PRIMARY KEY CLUSTERED
(
[FormType] ASC,
[KeyField] ASC,
[FieldName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-02-19 : 16:12:05

Try this:

select StockCode
      ,max(categ) as categ
      ,max(flavor) as flavor
      ,max(Type) as Type
  from (select KeyField as StockCode
              ,AlphaValue as categ
              ,AlphaValue as flavor
              ,AlphaValue as Type
              ,DateValue
              ,TimeStamp
          from dbo.AdmFormData
         where 0=1
        union all
        select KeyField as StockCode
              ,AlphaValue as categ
              ,null as flavor
              ,null as Type
              ,DateValue
              ,TimeStamp
          from dbo.AdmFormData
         where FieldName='categ'
        union all
        select KeyField as StockCode
              ,null as categ
              ,AlphaValue as flavor
              ,null as Type
              ,DateValue
              ,TimeStamp
          from dbo.AdmFormData
         where FieldName='flavor'
        union all
        select KeyField as StockCode
              ,null as categ
              ,null as flavor
              ,AlphaValue as Type
              ,DateValue
              ,TimeStamp
          from dbo.AdmFormData
         where FieldName='type'
       ) as a
 group by StockCode
         ,DateValue
         ,TimeValue
 order by StockCode
         ,DateValue
         ,TimeValue

Red section is to get the correct fieldtypes

Patyk
Yak Posting Veteran

74 Posts

Posted - 2015-02-19 : 16:31:16

Yes it work however the data shows like this: 3 lines per stock code. Can everything go to the same line. I mean one stock code one line? Or I should maybe create another view on top of this one? Thanks
StockCode categ flavor type
110100153 NULL TECH ONE NULL
110100153 NULL NULL SHAMPOO
110100153 MOISTURE NULL NULL

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-02-19 : 17:58:03

Try this:

select StockCode
      ,max(categ) as categ
      ,max(flavor) as flavor
      ,max(Type) as Type
  from (select KeyField as StockCode
              ,AlphaValue as categ
              ,AlphaValue as flavor
              ,AlphaValue as Type
              ,DateValue
              ,TimeStamp
          from dbo.AdmFormData
         where 0=1
        union all
        select KeyField as StockCode
              ,AlphaValue as categ
              ,null as flavor
              ,null as Type
              ,DateValue
              ,TimeStamp
          from dbo.AdmFormData
         where FieldName='categ'
        union all
        select KeyField as StockCode
              ,null as categ
              ,AlphaValue as flavor
              ,null as Type
              ,DateValue
              ,TimeStamp
          from dbo.AdmFormData
         where FieldName='flavor'
        union all
        select KeyField as StockCode
              ,null as categ
              ,null as flavor
              ,AlphaValue as Type
              ,DateValue
              ,TimeStamp
          from dbo.AdmFormData
         where FieldName='type'
       ) as a
 group by StockCode
         ,DateValue
         ,TimeValue
 order by StockCode
         ,DateValue
         ,TimeValue

If this doesn't do the job, try omitting the DateValue field

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources