Tag Info

New answers tagged

1

Give this a try,tested on my machine First make sure GROUP_CONCAT has plenty of space: SET SESSION group_concat_max_len = 1000000; SET @sql = NULL; SELECT GROUP_CONCAT(DISTINCT CONCAT( 'COUNT(DISTINCT CASE WHEN buyer_country= ''', buyer_country, ''' THEN transactions.buyer_id END) AS ', buyer_country ) )INTO @sql FROM ...


2

I don't have data on what links your tables so you might have to do a very little change to the ON clauses, but here it is: Creating tables with data CREATE TABLE #sales (PK int, Year int, Wk1 int, Wk2 int, Wk3 int, Wk4 int); GO INSERT INTO #sales VALUES (1,2012,9,5,3,4); INSERT INTO #sales VALUES (2,2013,7,2,6,3); INSERT INTO #sales VALUES ...


4

This sounds more like an UNPIVOT to me. SELECT SalesTable.Pk, CrossApplied.WeekYear, CrossApplied.Value FROM SalesTable CROSS APPLY (VALUES ('01'+CAST([Year] AS CHAR(40)), Week1), ('02'+CAST([Year] AS CHAR(40)), Week2), ('03'+CAST([Year] AS CHAR(40)), Week3), .......) CrossApplied ...


0

From my limited mysql knowledge, I don't think you can use PIVOT or ROW_NUMBER(), so here's a more generic suggestion on how to solve your problem: SELECT MIN(id) AS id, pd, MIN((CASE WHEN ordinal=1 THEN time END)) AS time1, MIN((CASE WHEN ordinal=2 THEN time END)) AS time2, MIN((CASE WHEN ordinal=3 THEN time END)) AS time3, ... ...


3

Here is a solution using PIVOT Declare @t table ( Comp_id int, Cntm_nb int, Seq_nb int, Amount decimal(19,4), Type varchar(10) ) Insert into @t values (1,1,1,2000,'rent'), (1,1,1,200,'charge'), (1,1,2,3000,'rent'), (1,1,2,300,'charge') Select Comp_id, Cntm_nb, Seq_nb, [rent] as [Rent Amount], [charge] as ...


4

You want to aggregate rows using a CASE condition within the aggregate. SELECT Comp_id, Cntm_nb, Seq_nb, SUM((CASE WHEN Type='rent' THEN Amount ELSE 0.0 END)) AS [Rent amount], SUM((CASE WHEN Type='charge' THEN Amount ELSE 0.0 END)) AS [Charge amount] FROM xyz GROUP BY Comp_id, Cntm_nb, Seq_nb; The CASE within the aggregate function adds a ...


0

The default length of a GROUP_CONCAT is 1024. Please run this mysql> SET group_concat_max_len = 1048576; before the query. This will allow for a 1M string as the maximum GROUP_CONCAT. If you want every session going forwards to have this, add this to my.cnf [mysqld] group_concat_max_len = 1M and run this mysql> SET GLOBAL group_concat_max_len ...



Top 50 recent answers are included