In order to sync multiple models along with custom pivot data, you need this: $user->roles()->sync([ 1 => [‘expires’ => true], 2 => [‘expires’ => false], … ]); Ie. sync([ related_id => [‘pivot_field’ => value], … ]); edit Answering the comment: $speakers = (array) Input::get(‘speakers’); // related ids $pivotData = array_fill(0, count($speakers), [‘is_speaker’ => true]); $syncData … Read more
The WM_CONCAT function (if included in your database, pre Oracle 11.2) or LISTAGG (starting Oracle 11.2) should do the trick nicely. For example, this gets a comma-delimited list of the table names in your schema: select listagg(table_name, ‘, ‘) within group (order by table_name) from user_tables; or select wm_concat(table_name) from user_tables; More details/options Link to … Read more
Remember that the MAX aggregate function will work on text as well as numbers. This query will only require the table to be scanned once. SELECT Action, MAX( CASE data WHEN ‘View’ THEN data ELSE ” END ) ViewCol, MAX( CASE data WHEN ‘Edit’ THEN data ELSE ” END ) EditCol FROM t GROUP BY … Read more
There are several ways that you can transform this data. In your original post, you stated that PIVOT seems too complex for this scenario, but it can be applied very easily using both the UNPIVOT and PIVOT functions in SQL Server. However, if you do not have access to those functions this can be replicated … Read more
yes, but why !!?? Select CustomerID, Min(Case DBColumnName When ‘FirstName’ Then Data End) FirstName, Min(Case DBColumnName When ‘MiddleName’ Then Data End) MiddleName, Min(Case DBColumnName When ‘LastName’ Then Data End) LastName, Min(Case DBColumnName When ‘Date’ Then Data End) Date From table Group By CustomerId
Dynamic SQL PIVOT: create table temp ( date datetime, category varchar(3), amount money ) insert into temp values (‘1/1/2012’, ‘ABC’, 1000.00) insert into temp values (‘2/1/2012’, ‘DEF’, 500.00) insert into temp values (‘2/1/2012’, ‘GHI’, 800.00) insert into temp values (‘2/10/2012’, ‘DEF’, 700.00) insert into temp values (‘3/1/2012’, ‘ABC’, 1100.00) DECLARE @cols AS NVARCHAR(MAX), @query AS … Read more
Install the additional module tablefunc once per database, which provides the function crosstab(). Since Postgres 9.1 you can use CREATE EXTENSION for that: CREATE EXTENSION IF NOT EXISTS tablefunc; Improved test case CREATE TABLE tbl ( section text , status text , ct integer — “count” is a reserved word in standard SQL ); INSERT … Read more
If you are using SQL Server 2005+, then you can use the PIVOT function to transform the data from rows into columns. It sounds like you will need to use dynamic sql if the weeks are unknown but it is easier to see the correct code using a hard-coded version initially. First up, here are … Read more
This basically is a pivot table. A nice tutorial on how to achieve this can be found here: http://www.artfulsoftware.com/infotree/qrytip.php?id=78 I advise reading this post and adapt this solution to your needs. Update After the link above is currently not available any longer I feel obliged to provide some additional information for all of you searching … Read more
There are several ways that you can transform data from multiple rows into columns. Using PIVOT In SQL Server you can use the PIVOT function to transform the data from rows to columns: select Firstname, Amount, PostalCode, LastName, AccountNumber from ( select value, columnname from yourtable ) d pivot ( max(value) for columnname in (Firstname, … Read more