New answers tagged

1

I don't really understand why you're trying to pivot anything, so maybe I'm missing your point. But try this: select id, name , regexp_replace(metadata, '.*"a":"([^"]+)".*', '\1') m_a , regexp_replace(metadata, '.*"b":"([^"]+)".*', '\1') m_b from foo; Doesn't rely on the order of the JSON attributes to always be the same. It's not completely safe - ...


5

This is a quite long query with 3 main operations involved: Partition By IndividualNumber using variables Pivot Partition' rows to columns using CASE Left Join with IndividualsTable and remove NULL You can look at the sample: SQL Fiddle Partition by IndividualNumber: The behavior of this query is similar to the ROW_NUMBER() Window Function available in ...


3

RDS doesn't let you install arbitrary C functions. It's a restricted environment. crosstab is part of the tablefunc extension. You may be able to CREATE EXTENSION tablefunc if it's on the whitelist but you won't be able to add new variants, even if the underlying C function is already loaded and approved.


3

You'll need dynamic SQL for this. Something like (untested with your columns and queries because you left those pretty vague): DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX); SET @columns = N''; SELECT @columns += N', ' + QUOTENAME(cName) FROM dbo.OtherTable; SET @sql = N' SELECT ' + STUFF(@columns, 1, 2, '') + ' FROM ( ... ) AS raw PIVOT ( ...


4

Although this has been answered, I wanted to point out a couple of things. Firstly, there is a SET LANGUAGE command which will save you a lot of typing. set langauge Portugeuse; This will localise the DATENAME function so you don't have to manually translate. Secondly, you are using SQL 2014, which has a format function (although it is slower than your ...


3

You must pivot your data. This can be done using the Pivot operator: SELECT ID, [date] , [1] as Hour1, [2] as Hour2, [3] as Hour3, [4] as Hour4 , total as [Sum] FROM ( SELECT * FROM data d CROSS APPLY (SELECT total = SUM([money]) FROM data WHERE [date] = d.[date] AND ID = d.[id]) a ) t PIVOT ( MAX([money]) FOR [hour] IN ...


1

Try something like this: SELECT ID ,[date] ,SUM(case when hour = 1 THEN money END) AS Hour1 ,SUM(case when hour = 2 THEN money END) AS Hour2 ,SUM(case when hour = 3 THEN money END) AS Hour3 ,SUM(case when hour = 4 THEN money END) AS Hour4 ,SUM(money) AS [Sum] FROM data GROUP BY ID, [date]


4

The PIVOT synxtax is: WITH query as ( your query... ) SELECT Dia_Da_Semana, [00:00], [01:00], [02:00], ... FROM query PIVOT ( MAX(Count_Reps_Abertas) FOR Hora IN ([00:00], [01:00], [02:00], ...) ) as piv ; You can refer to: Using PIVOT and UNPIVOT Your query goes to the query CTE. This query/CTE must return 3 columns: row name = ...



Top 50 recent answers are included