Tag Info

New answers tagged

1

select d1.*, d2.SalesCode, d2.UnitPrice from Data d1, Data d2 where d1.Variant = d2.Variant and d1.ItemNo = d2.ItemNo and d1.SalesCode = 'SRP' and d2.SalesCode = 'WSP';


3

CREATE TABLE #Data ( ItemNo VARCHAR(10), Variant INT, SalesCode VARCHAR(30), UnitPrice INT ) GO INSERT INTO #Data ( ItemNo, Variant, SalesCode, UnitPrice ) VALUES ( 'A', 1, 'SRP', 100 ), ( 'A', 2, 'SRP', 200 ), ( 'A', 1, 'WSP', 50 ), ( 'A', 2, 'WSP', 100 ), ( 'B', 1, 'SRP', 300 ), ...


0

I have tried to make it using GROUP BY in MYSQL, Hope this helps! SELECT channelid, deviceid, pointtime, SUBSTRING_INDEX(GROUP_CONCAT(pointvalue ORDER BY pointid ASC SEPARATOR '^'),'^',1) AS pointid3_1, SUBSTRING_INDEX(GROUP_CONCAT(pointvalue ORDER BY pointid ASC SEPARATOR '^'),'^',-1) AS pointid3_11 FROM Think.pointhistory GROUP BY ...


0

You can use REPLACE on the date column in the @PivotColumnHeaders string to build the concatenated column. SELECT @PivotColumnHeaders = COALESCE( @PivotColumnHeaders + ',[' + cast(SystemFullName as Nvarchar) + ']', '[' + cast(SystemFullName as varchar)+ ']' ) FROM System WHERE (@SelectedSystemIDs IS NULL OR System.ID IN(select * from ...


0

Change your query to this: SELECT pvt.ID ,[198] = isnull([198],0) ,[281] = isnull([281],0) ,[280] = isnull([280],0) ,[295] = isnull([295],0) ,[296] = isnull([296],0) ,[297] = isnull([297],0) FROM ( SELECT [ID], [Catalog], [Grade] From data ) as c PIVOT ( SUM([Grade]) FOR [Catalog] IN ([198], [281], [280], [295], [296], ...


3

Try this Query; Select s.ItineraryID, R.ShortName as RoadTripName, sum(EstimatedMiles) as TotalMiles, count(itineraryid) as TotalDays, SUM(case DayType.DayTypeCode when 'XX' then 1 else 0 END ) as 'Planning/Prep', SUM(case DayType.DayTypeCode when 'SE' then 1 else 0 END ) as 'Short Event', SUM(case DayType.DayTypeCode when 'LE' then 1 else 0 END ) as 'Long ...



Top 50 recent answers are included