I have a table which contains Job, City and PersonName (who works in that city). I want to return all jobs and cities even if there is no one working in that city. This is a simple example : Creation of the table : My Query My result But I would like to have : I have no clue how
Tag: tsql
Create custom field by subtracting column values from two different rows based on multiple IDs
I have to create custom column by subtracting column values from two different rows based on multiple IDs. Data set is like below: ID2 is different for each date. Now I have to display custom field like below: Repeated for other ID2 also. Please suggest how should I proceed. I tried using Cross Join but but not giving desirable result.
Update table value from a second table by joining a third table
I have 3 tables, TableA, TableB and TableC I want to migrate the data from TableA column DateA to TableB column DateB, only if the date field is null in table B (I don’t want to overwrite any …
Reading JSON array containing JSON object into rows
Based from this answer I found one problem. JSON object is return as NULL. Suppose that I have a JSON like this: { “array_in_json”: [ { “number”: 1, “character”: “A”, “some_object”: { “…
System Catalog vs Information Schema
When looking at SQL Server Management Studio (SSMS), which one is better and why? Are there cases where we should use one over the other? I can’t tell the difference between them. Answer INFORMATION_SCHEMA is there for compatibility, it doesn’t expose all the information about objects on the instance. sys however, fully exposes any relevant information, though you do need
Top 3 Values in the Same Row
I am working on Steam data and need to find the top 3 tags people used for each game but the problem is all the tag values are in the same row for each game like the image below (appid column then 370 column for each tag and values). Result can be anything like 200,300,400 or column name plus tag
Couldn’t return output with SRC Key in an INSERT statement
I want the OUTPUT clause to return both the source key and the target key like below : I am having this following error : Answer To be clear, in INSERT, UPDATE, and DELETE statements, you can only refer to columns from the target table in the OUTPUT clause. In a MERGE statement you can refer to columns from both
Single query to split out data of one column, into two columns, from the same table based on different criteria [SQL]
I have the following data in a table, this is a single column shown from a table that has multiple columns, but only data from this column needs to be pulled into two column output using a query: Required result: distinct values based on the first 13 characters of the data, split into two columns based on “long data”, and
How to select and join field with max date?
I have two tables joined on RECID and AAATRANSPORTORDERRECID : AAATRANSPORTTABLE AAALTLCHANGEREQUEST I need to select the record shown from AAATRANSPORTTABLE and join the AAALTLCHANGEVALUE value for the most recent CREATEDDATETIME from AAALTLCHANGEREQUEST. My query is as below: It produces these results: My desired output is Answer The problem is you are selecting max(l.CREATEDDATETIME) in your select query but you
How to count the number of occurences for a specific value from a range of columns using T-SQL?
I have a table (t1) as shown below that contains single-character values in all columns named v%: I need to count the number of occurrences for “O” and “E” and display them in 2 additional columns. How can I do this? Expected Output: Answer I would use apply and aggregation: