New answers tagged

4

Sure, you can generate the list of years in dynamic SQL, but I suggest passing them as numbers (just one less thing that can go wrong if people pass in bad data, and I'm sure you didn't really mean to hard-code the values inside the procedure, defeats the purpose): DECLARE @year1 smallint, @year2 smallint SET @year1 = 2014; SET @year2 = 2016; DECLARE ...


4

Basically it's a crosstab query: PostgreSQL Crosstab Query The dynamic result type is a problem, though. ... is it possible to create a dynamic query which will output the right thing regardless of the number of runs? No. Currently (including Postgres 9.6) not possible with a single SELECT statement. Not unless you know the return type at call time ...


3

Using the PIVOT clause, you can achieve the result like this: SELECT Vehicle_ID, Doors, [Engine Size], [Tire Size], [Color] FROM dbo.YourView PIVOT ( MAX(Value) FOR Property IN (Doors, [Engine Size], [Tire Size], [Color]) ) AS p ; Now, to join the results to your main vehicle table, you can use the above as a derived table (same as ...


1

The any Sql DBMS fast runnig pivot is just (MS SQL, you may need replace [] with "" or something for other Sql DBMS) select Vehicle_ID ,max(case Property when 'Doors' then Value end) as [Doors] ,max(case Property when 'Engine Size' then Value end) as [Engine Size] --... from theView group by Vehicle_ID Which MS SQL abbreviates to select ...


1

Answering my own question because I found an answer on StackOverflow that was immensely helpful: http://stackoverflow.com/a/1343174/123729 Using the logic in the question above, the query that works for me doesn't need a PIVOT operation, just a GROUP BY and MIN (or MAX) with a CASE statement, like so: Select Vehicle_ID, Min(Case Property When 'Doors' ...


3

Table and sample data CREATE TABLE dbo.Data ( [No] integer NOT NULL, Name varchar(4) NOT NULL, Relation varchar(2) NOT NULL ); INSERT dbo.Data ([No], Name, Relation) VALUES (1, 'A', 'P'), (1, 'B', 'GO'), (1, 'C', 'JB'), (2, 'AH', 'P'), (3, 'TY', 'P'), (3, 'DO', 'JB'), (3, 'DO1', 'JB'), (3, ...


1

[], pivot are hints it's MS SQL. Then select [Cost Center] , [Node1] = max(case rn when 1 then [Function] end) , [Node2] = max(case rn when 2 then [Function] end) , [Node3] = max(case rn when 3 then [Function] end) , [Node4] = max(case rn when 4 then [Function] end) , [Node5] = max(case rn when 5 then [Function] end) , [Node6] = max(case rn when ...


1

if is is just two then select name , min(Contact No) as [cont 1] , max(Contact No) as [cont 2] from table group by name


1

Here's an example which does what you want and will get you started with using PIVOT. Sorry it is a bit rushed, but hopefully it will get you started and show you how it can be done fairly easily. There will be limitations and I would fully advise researching and playing with the pivot functions because they are really powerful. -- Some sample data to work ...


1

This Query is the answer to my question. It is dynamic. Declare @Skill NVARCHAR(MAX), @Degree_Field NVARCHAR(MAX), @Experience NVARCHAR(MAX), @query NVARCHAR(MAX) select @Skill = STUFF(( select SEQ From ( ...


3

You can UNPIVOT and then PIVOT. Half of it is below (untested): WITH P1_Src As ( SELECT ID_Skill, Val, Col FROM Job_Skill UNPIVOT (Val FOR Col IN (Min_Job, Idea_Job, Max_Job)) AS U ), P1 AS ( SELECT Col, [1], [2], [7], [8] FROM P1_Src PIVOT (MAX(Val) FOR ID_Skill IN ([1], [2], [7], [8])) AS P ) SELECT * FROM P1 You would need to apply a case expression ...


3

Will this work for you? It's not dynamic as you need to know what the skills are ahead of time, but it gets the job done. I've added another skill to show you how to do that. You only need to change it in one place. DECLARE @Table TABLE ( Skill VARCHAR(10) NOT NULL , MinValue INT NOT NULL , IdealValue INT NOT NULL , MaxValue INT ...


0

Example pivot tracking table CREATE TABLE IF NOT EXISTS `mail_track` ( `ID` int(10) NOT NULL auto_increment, MemberID mediumint(7) default NULL, `MessageTitle` varchar(20) default NULL, `Type` varchar(6) default NULL, `URLID` varchar(15) default NULL, `DateTime` datetime default NULL, `IP` varchar(15) default NULL, PRIMARY KEY (`ID`), KEY `DateTime` ...



Top 50 recent answers are included