September 30, 2020 at 8:31 am
Hi SSC,
I'm mentally stuck with this one. Essentially I want 2 rows with all the measures related to the Internal_No but I'm stuck figuring it out. Here's how far I got:
USE [Database]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ARCHIVE](
[Time_Inv] [datetime] NULL,
[Rep_Date] [datetime] NULL,
[SalesOrg] [varchar](50) NULL,
[TGA] [varchar](50) NULL,
[KG_Picture] [varchar](50) NULL,
[SSL_Type] [varchar](50) NULL,
[Rep_Type] [varchar](50) NULL,
[Inv_No] [varchar](50) NULL,
[runtime] [varchar](50) NULL,
[TimeMode] [varchar](50) NULL,
[Inv_Org] [varchar](50) NULL,
[Replace_No] [varchar](50) NULL,
[Internal_No] [varchar](50) NULL,
[variant] [varchar](50) NULL,
[runtime2] [varchar](50) NULL,
[User_ID] [varchar](50) NULL,
[Product_Detail] [varchar](50) NULL,
[Measure] [varchar](50) NULL,
[Measure_Value] [varchar](50) NULL
) ON [PRIMARY]
GO
USE [Database]
GO
INSERT INTO [dbo].[ARCHIVE]
([Time_Inv]
,[Rep_Date]
,[SalesOrg]
,[TGA]
,[KG_Picture]
,[SSL_Type]
,[Rep_Type]
,[Inv_No]
,[runtime]
,[TimeMode]
,[Inv_Org]
,[Replace_No]
,[Internal_No]
,[variant]
,[runtime2]
,[User_ID]
,[Product_Detail]
,[Measure]
,[Measure_Value])
VALUES
('2019-01-04 00:00:00.000','2015-03-09 00:00:00.000','00098','0','32206','D3','7','0912','77','Advancement','13000','0915','023138838','EXCELLENT','235380','FLO','1857~55008','Wage','-198.94')
,('2019-01-04 00:00:00.000','2015-03-09 00:00:00.000','00098','0','32206','D3','7','0912','77','Advancement','13000','0915','023138838','EXCELLENT','235380','FLO','1857~55008','runtime Km','235380')
,('2019-01-04 00:00:00.000','2015-03-09 00:00:00.000','00098','0','32206','D3','7','0912','77','Advancement','13000','0915','023138838','EXCELLENT','235380','FLO','1857~55008','runtime Mte','77')
,('2019-01-04 00:00:00.000','2015-03-09 00:00:00.000','00098','0','32206','D3','7','0912','77','Advancement','13000','0915','023138838','EXCELLENT','235380','FLO','1857~55008','Count','1')
,('2019-01-04 00:00:00.000','2015-03-09 00:00:00.000','00098','0','32206','D3','7','0912','77','Credit','13000','0915','023138838','EXCELLENT','235380','FLO','1857~55008','Wage','-198.94')
,('2019-01-04 00:00:00.000','2015-03-09 00:00:00.000','00098','0','32206','D3','7','0912','77','Credit','13000','0915','023138838','EXCELLENT','235380','FLO','1857~55008','runtime Km','235380')
,('2019-01-04 00:00:00.000','2015-03-09 00:00:00.000','00098','0','32206','D3','7','0912','77','Credit','13000','0915','023138838','EXCELLENT','235380','FLO','1857~55008','runtime Mte','77')
,('2019-01-04 00:00:00.000','2015-03-09 00:00:00.000','00098','0','32206','D3','7','0912','77','Credit','13000','0915','023138838','EXCELLENT','235380','FLO','1857~55008','Count','1')
GO
SELECT Internal_No
,CASE WHEN Measure = 'Wage' then Measure_Value END AS [Wage]
,CASE WHEN Measure = 'runtime Km' then Measure_Value end as [runtime_km]
,CASE WHEN Measure = 'runtime Mte' then Measure_Value end as [runtime_hrs]
,CASE WHEN Measure = 'Count' then Measure_Value END AS [Cnt]
from dbo.ARCHIVE
Any help is really appreciated.
September 30, 2020 at 9:34 am
Looks like you just need to add an aggregate and group by
SELECT a.Internal_No
, a.TimeMode
, Wage = MAX( CASE WHEN a.Measure = 'Wage' THEN a.Measure_Value END )
, runtime_km = MAX( CASE WHEN a.Measure = 'runtime Km' THEN a.Measure_Value END )
, runtime_hrs = MAX( CASE WHEN a.Measure = 'runtime Mte' THEN a.Measure_Value END )
, Cnt = MAX( CASE WHEN a.Measure = 'Count' THEN a.Measure_Value END )
FROM dbo.ARCHIVE AS a
GROUP BY a.Internal_No, a.TimeMode;
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply