New answers tagged

0

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 ( ...


2

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 to ...


0

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` ...


2

You have an extra unnecessary subquery check, and your subquery seems to only return a rowset if there is a single value in it, so you may want to use = instead of in. You can achieve the same by doing this: UPDATE product SET deleted_at = NOW() WHERE id = ( SELECT entity_id FROM product_media WHERE media_id = ? GROUP BY entity_id ...


2

This is a tricky one. crosstab() expects one (or no) value per category for each row_name. We can work around this restriction like this: SELECT id , COALESCE(cat01, max(cat01) OVER w) , COALESCE(cat02, max(cat02) OVER w) , COALESCE(cat03, max(cat03) OVER w) FROM crosstab( 'SELECT id::text || row_number() OVER (PARTITION BY id, category ...


1

Take a look here for an example of how to use the CROSSTAB function. Also, take a good look at Erwin Brandstetter's post in the same thread and links within (especially the "Basics for crosstab():" link. Be careful with NULLs (see the discussion in link). If you're not using a PostgreSQL version compiled from source, then all you have to do to access the ...


0

What you look at is a crosstab. Assuming your table is called "Fact_Table", write: select * from crosstab('select id, category, value from Fact_Table') Also see http://www.postgresql.org/docs/9.5/static/tablefunc.html if you look for other variants.


5

Assuming 2016-04-05 0:27:15 instead of 2016-04-05 1:27:15 in the underlying table, the question would make more sense to me: CREATE TABLE tbl (created_at timestamp, status text); INSERT INTO tbl VALUES ('2016-04-05 00:27:15', 'info') , ('2016-04-05 03:27:15', 'info') , ('2016-04-05 05:27:15', 'warn') , ('2016-04-05 10:27:15', 'info') , ('2016-04-05 ...


4

To answer this, I did the following. (I found the following three threads helpful - 1, 2 and 3. I also found the documentation on generate_series and CROSSTAB here and here respectively to be useful). This should work on 9.1 - not tested, but the documentation indicates that no post-9.1 stuff is being used. Created a table: ntest=# create table ...


0

If you have to do it on the SQL Server side, you can use GROUP BY with a CASE statement: SELECT PERIOD, EMPL_NMBR, EMPL_NAME , IN_1 = MAX(CASE WHEN DAYX = 1 THEN [IN] END) , OUT_1 = MAX(CASE WHEN DAYX = 1 THEN [OUT] END) , IN_2 = MAX(CASE WHEN DAYX = 2 THEN [IN] END) , OUT_2 = MAX(CASE WHEN DAYX = 2 THEN [OUT] END) , IN_3 = MAX(CASE WHEN ...


5

You can handle this dynamically using XML, XQuery, and the FLWOR Statement and Iteration syntax: ;WITH blob ([data]) AS ( -- *** BEGIN Replace with your query *** SELECT so.[object_id], so.[name], so.[schema_id], so.[is_ms_shipped], so.[type], IIF(so.[type]='S', NULL, 'NotNull') AS [NullTest] FROM master.sys.objects so ...


6

Depending on how strict your formatting requirements are, you could use the FOR XML syntax. This will generate an XML document for your results, which will give you the field name and value on each row. You could further manipulate the results to de-XML the results back into a more customized format if your example format is a strict requirement. There are ...


4

First a partial answer to your question. You could use dynamic SQL. This would be a starting point: SELECT ', CONCAT(''' + col.name + ''', ISNULL(['+col.name+'],NULL))' FROM sys.all_objects obj JOIN sys.all_columns col ON obj.object_id = col.object_id JOIN sys.schemas sch ON obj.schema_id = sch.schema_id WHERE obj.type IN ('U','S','V') AND ...


6

The following is a bastardized way of doing this, but is useful since it shows how you can use the very powerful "Regular expressions" search-and-replace functionality included in SQL Server Management Studio. Once you get the hang of how this works, it can become something you use almost every day. "Drag-and-drop" the list of columns from the Object ...


3

The columns ABSENT, LATE and PRESENT in your output represent totals per student, whereas the date columns represent details per student, and details are the pivoted columns. In order to get such output with PIVOT, your source must provide totals on the same row with details. The source in your query is not providing that kind of information. It is grouping ...



Top 50 recent answers are included