I currently have a resultset that looks like this:  Title                   Value Start Time              1998-01-04 01:38:00.000 End Time                1998-01-04 01:43:00.000 Product Name            TESR                                Run Time                325 Delay Time              0 Shutdowns               0 Shift                   2 Gross MSF               3.791682 Start Time              1998-08-12 12:43:00.000 End Time                1998-08-13 16:48:00.000 Product Name            FCC                                Run Time                320 Delay Time              0 Shutdowns               0 Shift                   1 Gross MSF               3.791682 Start Time              1999-11-04 02:43:00.000 End Time                1999-11-04 03:48:00.000 Product Name            1/2" TESR                                Run Time                325 Delay Time              0 Shutdowns               0 Shift                   2 Gross MSF               3.791682   What I would like to end up with is this:  Start Time              1998-01-04 01:38:00.000      1998-08-12 12:43:00.000 End Time                1998-01-04 01:43:00.000      1998-08-13 16:48:00.000 Product Name            TESR                         FCC Run Time                325                          325 Delay Time              0                            0 Shutdowns               0                            0 Shift                   2                            2 Gross MSF               3.791682                     3.121   Basically looking like a spreadsheet with each group of records being put in a column to the right.  The original result is from a pivot of a flat table joined to a sub table that puts a title to the property fields. It is running on SQL Server 2008 R2. The data is coming from an HMI system and the client wants to see the data in the "spreadsheet" format.
27629
I have a SQL Server instance that has a linked server to an Oracle server. There is a table on the Oracle server called PersonOptions which contains the following data:  ╔══════════╦══════════╗ ║ PersonID ║ OptionID ║ ╠══════════╬══════════╣ ║        1 ║ A        ║ ║        1 ║ B        ║ ║        2 ║ C        ║ ║        3 ║ B        ║ ║        4 ║ A        ║ ║        4 ║ C        ║ ╚══════════╩══════════╝   I need to pivot that data so the results are:  ╔══════════╦═════════╦══════════╦══════════╗ ║ PersonID ║ OptionA ║ Option B ║ Option C ║ ╠══════════╬═════════╬══════════╬══════════╣ ║        1 ║       1 ║        1 ║          ║ ║        2 ║         ║          ║        1 ║ ║        3 ║         ║        1 ║          ║ ║        4 ║       1 ║          ║        1 ║ ╚══════════╩═════════╩══════════╩══════════╝   Any suggestions?
112994
I have a balances table in PostgreSQL 9.3 that looks like this:  CREATE TABLE balances (   user_id INT , balance INT , as_of_date DATE );  INSERT INTO balances (user_id, balance, as_of_date) VALUES   (1, 100, '2016-01-03') , (1,  50, '2016-01-02') , (1,  10, '2016-01-01') , (2, 200, '2016-01-01') , (3,  30, '2016-01-03');   It only contains balances for dates that a user has made a transaction. I need it to contain a row for each user with their balance on each date in a given date range.   If the user does not have a row for a given date in the range, I need to use their balance from the day before. If the user created their account after a given date in the range, I need to avoid creating a row for that user/date combination.   I can reference an accounts table to get users' create_date:  CREATE TABLE accounts (   user_id INT , create_date DATE );  INSERT INTO accounts (user_id, create_date) VALUES   (1, '2015-12-01') , (2, '2015-12-31') , (3, '2016-01-03');   My desired result looks like this:  +---------+---------+--------------------------+ | user_id | balance |        as_of_date        | +---------+---------+--------------------------+ |       1 |     100 | 2016-01-03T00:00:00.000Z | |       1 |      50 | 2016-01-02T00:00:00.000Z | |       1 |      10 | 2016-01-01T00:00:00.000Z | |       2 |     200 | 2016-01-03T00:00:00.000Z | |       2 |     200 | 2016-01-02T00:00:00.000Z | |       2 |     200 | 2016-01-01T00:00:00.000Z | |       3 |      30 | 2016-01-03T00:00:00.000Z | +---------+---------+--------------------------+   Note that rows have been added for user 2 for 2016-01-02 and 2016-01-03, carrying over the previous balance from 2016-01-01; and that no rows have been added for user 3, who was created on 2016-01-03.  To generate a series of dates in a date range, I know I can use:  SELECT d.date FROM GENERATE_SERIES('2016-01-01', '2016-01-03', '1 day'::INTERVAL) d   ...but I am struggling with LEFT JOINing that series with each set of rows grouped by user_id.
82651
I have an output of a query as follows:  bid  | code | amount        | ----------------------------- 2915 | BF   |   -10700.00   | 2915 | YQ   |   -300.00     | 2915 | YR   |   0.00        | 2915 | YM   |   0.00        | 2915 | WO   |   -153.00     | 2915 | IN   |   -329.00     | 2915 | K3   |   0.00        | 2915 | CUTE |   -50.00      | -----------------------------   I need to convert the values in code column to individual columns with column name as corresponding code and amount shall be the value.  bid  | BF       | YQ     | ... | CUTE  | ---------------------------------------- 2915 | -10700.00| -300.00| ... | -50.00|   The issue is that the result of my initial query has dynamic codes and hence need to generate columns accordingly.  Already tried the following logic but couldn't achieve the goal:   , ,   All the above links use some expression or the other to generate the columns.   My original resulting rows are result of simple select on multiple joined tables, and has no calculation or expression scope.  Kindly suggest some pointer or hint on how to achieve this.  Many thanks in advance.
13501
How do I get:  id       Name       Value 1          A          4 1          B          8 2          C          9   to  id          Column 1          A:4, B:8 2          C:9
122494
Consider:  with days as (select day::date from generate_series(date '2013-01-01', date '2013-01-01' + 365, interval '1 day' day) day )  select 'Inspections'::text as data_label, count(i.reporting_id) as daily_count, d.day as date_column from days d left join inspection i on i.close_case_date = d.day group by d.day order by d.day   This returns a set that looks like this:  data_label | daily_count | date_column Inspections    1           01/01/13 Inspections    2           01/02/13 Inspections    4           01/04/13 Inspections    8           01/06/13   Note the 1 and 2 day gaps in the record set. I need to generate a set with those values filled in with 0 like so:  data_label | daily_count | date_column Inspections    1           01/01/13 Inspections    2           01/02/13 Inspections    0           01/03/13 Inspections    4           01/04/13 Inspections    0           01/05/13 Inspections    8           01/06/13   How would I do this? I am not much of a Database Developer and am new to Postgres, so I am not sure where to start and can not find anything on how to accomplish this objective.
114266
I tried to find this question somewhere else on here, and found a couple that were similar to it -- but all the answers seemed pretty convoluted, and I think it was surprising to me because I just imagined that SQL would have a way easier way to do this.  Essentially, I'm using a GROUP BY to group two values on top of each other. The coding is simple and works well, where I get these results using WITH ROLLUP at the end:  Type    Person  Count Type A  Person 1    10 Type A  Person 2    91 Type A  Person 3    13 Type A  Person 4    10 Type A  Person 5    2 Type A  NULL        126 Type B  Person 6    16 Type B  Person 7    63 Type B  Person 8    15 Type B  Person 9    22 Type B  Person 10   16 Type B  Person 11   3 Type B  Person 12   20 Type B  Person 13   44 Type B  NULL        198 NULL    NULL        360   Is there an easy way to substitute something saying if "NULL" is in the "Person" column, then make NULL AS "Subtotal" and if "NULL" is in the "Type" column, NULL AS "Grand Total?"  And if that's weirdly complicated, I'd be totally fine with just calling all of them "Total."  Thanks!
18391
I'm trying to write a query that extracts and transforms data from a table and then insert those data into another table. Yes, this is a data warehousing query and I'm doing it in MS Access. So basically I want some query like this:  INSERT INTO Table2(LongIntColumn2, CurrencyColumn2) VALUES   (SELECT LongIntColumn1, Avg(CurrencyColumn) as CurrencyColumn1 FROM Table1 GROUP BY LongIntColumn1);   I tried but get a syntax error message.  What would you do if you want to do this?
14862
I am trying to query two tables and get results like the following:  Section    Names shoes      AccountName1, AccountName2, AccountName3 books      AccountName1   The tables are:  CREATE TABLE dbo.TableA(ID INT, Section varchar(64), AccountId varchar(64));  INSERT dbo.TableA(ID, Section, AccountId) VALUES (1 ,'shoes','A1'), (2 ,'shoes','A2'), (3 ,'shoes','A3'), (4 ,'books','A1');  CREATE TABLE dbo.TableB(AccountId varchar(20), Name varchar(64));  INSERT dbo.TableB(AccountId, Name) VALUES ('A1','AccountName1'), ('A2','AccountName2'), ('A3','AccountNAme3');   I saw a few questions answered saying to use "XML PATH" and "STUFF" to query the data to get the results I am looking for, but I think there is something missing.  I have tried the below query and get the error message:     Column 'a.AccountId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.   I don't have it in the SELECT clause of either query, but I assume the error is because AccountId is not unique in TableA.  Here is the query I am currently trying to get working correctly.  SELECT section, names= STUFF((     SELECT ', ' + Name FROM TableB as b  WHERE AccountId = b.AccountId FOR XML PATH('')), 1, 1, '') FROM TableA AS a GROUP BY a.section
93807
I have an SQL 2005 table, let's call it Orders, in the format:  OrderID, OrderDate,  OrderAmount 1,       25/11/2008, 10 2,       25/11/2008, 2 3,       30/1002008, 5   Then I need to produce a report table showing the ordered amount on each day in the last 7 days:  Day,        OrderCount, OrderAmount 25/11/2008, 2,          12 26/11/2008, 0,          0  27/11/2008, 0,          0  28/11/2008, 0,          0 29/11/2008, 0,          0 30/11/2008, 1,          5   The SQL query that would normally produce this:  select count(*), sum(OrderAmount)     from Orders     where OrderDate&gt;getdate()-7     group by datepart(day,OrderDate)   Has a problem in that it will skip the days where there are no orders:  Day,        OrderCount, OrderAmount 25/11/2008, 2,          12 30/11/2008, 1,          5   Normally I would fix this using a tally table and outer join against rows there, but I'm really looking for a simpler or more efficient solution for this. It seems like such a common requirement for a report query that some elegant solution should be available for this already.   So: 1. Can this result be obtain from a simple query without using tally tables?  and 2. If no, can we create this tally table (reliably) on the fly (I can create a tally table using CTE but recursion stack limits me to 100 rows)?
61610
I have a query which selects a max value over all the values of in a datetime column (dtimesent).  select dtimesent, max(ResponseTime) AS maxtime FROM allrequests  where Request like '%Requestsa%' group by dtimesent   I need to get the value of the request colmn for each max response time in each group by. Is there any easy way to do this?  There can be multiple rows per dtimesent value so I can't just include the request column in the group by,  Many thanks,
4375
Having the following data in a table:  ID     Category   Value 1234   Cat01      V001 1234   Cat02      V002 1234   Cat03      V003 1234   Cat03      V004 1234   Cat03      V005   I want to have the following output:  ID     Cat01  Cat02   Cat03 1234   V001   V002    V003 1234   V001   V002    V004 1234   V001   V002    V005   The output I want to achieve is a kind of pivot table where I have all the values vertically in a table and I want to have those values, horizontally, having the category as a column. But there are some categories that have multiples values, in that case, I need to repeat the values of all other categories and create a row per each repeated value  How can it be done in PostgreSQL?
10508
Problem: I have an address field from an Access database which has been converted to SQL Server 2005. This field has everything all in one field. I need to parse out the address's individual sections into their appropriate fields in a normalized table. I need to do this for approximately 4,000 records, and it needs to be repeatable. Assumptions:  Assume an address in the US (for now)  assume that the input string will sometimes contain an addressee (the person being addressed) and/or a second street address (i.e. Suite B)  states may be abbreviated  zip code could be standard 5 digits or zip+4  there are typos in some instances   UPDATE: In response to the questions posed, standards were not universally followed; I need need to store the individual values, not just geocode and errors means typo (corrected above) Sample Data:  A. P. Croll &amp; Son  2299 Lewes-Georgetown Hwy, Georgetown, DE 19947  11522 Shawnee Road, Greenwood DE  19950  144 Kings Highway, S.W. Dover, DE  19901  Intergrated Const. Services 2 Penns Way Suite 405 New Castle, DE  19720  Humes Realty 33 Bridle Ridge Court, Lewes, DE  19958  Nichols Excavation 2742 Pulaski Hwy Newark, DE  19711  2284 Bryn Zion Road, Smyrna, DE  19904  VEI Dover Crossroads, LLC  1500 Serpentine Road, Suite 100 Baltimore MD  21  580 North Dupont Highway Dover, DE  19901  P.O. Box 778 Dover, DE  19903
41793
I have a child table that is something like this:  [Cust Date Table]  | Customer ID | Some Date  | Balance | +-------------+------------+---------+ |           1 | 2012-04-30 |   20.00 | |           1 | 2012-03-31 |   50.00 | |           2 | 2012-04-30 |    0.00 | |           2 | 2012-03-31 |   10.00 |  |           3 | 2012-03-31 |   60.00 | |           3 | 2012-02-29 |   10.00 |   I would like to be able to get a result set like this - one record for each client with the latest date:  | Customer ID | Some Date  | Balance | +-------------+------------+---------+ |           1 | 2012-04-30 |   20.00 |  |           2 | 2012-04-30 |    0.00 | |           3 | 2012-03-31 |   60.00 |   I know that I can do this for each individual "Customer ID" with the following SQL (SQL Server syntax):  select top 1  [Some Date], [Customer ID], [Balance] from [Cust Date Table] where [Customer ID] = 2 order by [Some Date] desc   | Customer ID | Some Date  | Balance | +-------------+------------+---------+ |           2 | 2012-04-30 |    0.00 |   But I'm not sure how to get all three of the records I want.  I'm not sure if this is a situation that calls for a sub-query or something else.    Please note that the max date can be different for any given [Customer ID], (in this example, customer 3's maximum date is 2012-03-31 whereas the other records have a max date of 2012-04-30). I have tried  select [Customer ID], MAX([Some Date]) AS [Latest Date], Balance  from [Cust Date Table]  group by [Customer ID], Balance;    The problem is this doesn't return just the one row for each customer - it returns multiple rows.
8230
I often need to select a number of rows from each group in a result set.  For example, I might want to list the 'n' highest or lowest recent order values per customer.  In more complex cases, the number of rows to list might vary per group (defined by an attribute of the grouping/parent record). This part is definitely optional/for extra credit and not intended to dissuade people from answering.  What are the main options for solving these types of problems in SQL Server 2005 and later? What are the main advantages and disadvantages of each method?  AdventureWorks examples (for clarity, optional)   List the five most recent recent transaction dates and IDs from the TransactionHistory table, for each product that starts with a letter from M to R inclusive. Same again, but with n history lines per product, where n is five times the DaysToManufacture Product attribute. Same, for the special case where exactly one history line per product is required (the single most recent entry by TransactionDate, tie-break on TransactionID.
34846
I have a TEMPORARY table containing pairs of key/value as  id   col1      col2        type         pair 1    key1      key2        key          1 2    value1    value2      value        1 3    key3      key4        key          2 4    value3    value4      value        2 5    key5      key6        key          3 6    value5    value6      value        3   I want to mix the key/values as  pair    key_col1     key_col2      value_col1      value_col2 1       key1         key2          value1          value2 2       key3         key4          value3          value4 3       key5         key6          value5          value6   Since, it is a temporary table, I cannot use JOIN. How can I SELECT from this table to have pairs of key/value in each row?
28961
Table #01 Status:  StatusID    Status -----------------------  1          Opened  2          Closed  3          ReOpened  4          Pending   Table #02 Claims:  ClaimID     CompanyName StatusID -------------------------------------- 1               ABC     1 2               ABC     1 3               ABC     2 4               ABC     4 5               XYZ     1 6               XYZ     1   Expected Result:  CompanyName TotalOpenClaims TotalClosedClaims TotalReOpenedClaims TotalPendingClaims -------------------------------------------------------------------------------- ABC                 2           1                      0               1 XYZ                 2           0                      0               0   How do I need to write the query so that I could get the result as expected?
42372
I'd like to select the most recent row in a MS SQL Server 2008 R2 database based on a couple of factors.  I've seen a lot of people want to do the same thing online, and a lot of different proposed solutions, but most seem overcomplicated, and I can't get anything to work properly or do what I want.  I have data in a table where a particular thing (Room) will have a name, and a most recent status (AnalogValue; it correlates to online status) with a corresponding date/time (LogTimeStanp).  I'm most interested in the current online status for each room, so I just need the most recent row for each room.   There can be a number of rooms, for an example 1 - 5,000 (there's no imposed upper limit). There is a lot of data that pulls back when I look at the historic online status. There is other data in this table that I'm not interested in for this query. I have to join tables to get a readable room name, otherwise I just have a GUID. I'm not sure if I need to use MAX(), SELECT DISTINCT, Group by, or something else.  I have not been able to get these to work to my satisfaction.   This code does not work, but should give you an idea of what I want to do:  SELECT r.RoomName, a.AttributeID, a.AnalogValue, max(a.LogTimeStamp)   FROM CRV_AttributeLog a join CRV_Rooms r on a.RoomID=r.RoomID   where a.AttributeID like 'online_status'   My intent is to get the AnalogValue with the max (most recent) LogTimeStamp per individual room.  I have gotten a query to run with group by, but it doesn't seem to be grouping it (and certainly not by room name), so I guess I am doing it wrong.  Update 1, 2, 3, 4  Added pics for context.  Also, this query will form the basis of a report, so it has to be entirely referential, that's why I just want "the latest" online status.  Further complicating things, "the latest" may not have happened today, this week, or this month.  But it's in there, somewhere, sometime.  Each row in CRV_AttributeLog will have its own AttributeID and AnalogValue, and the row is made unique by the time stamp (LogTimeStamp).  Otherwise, it's entirely possible for AttributeID and AnalogValue to be matching across rows (and that's OK).  This table is logging information about when a few different somethings change: online status, if there is an error, etc.  ONLINE_STATUS can change from 0-2 (Offline, partially online, online) and will change between them periodically.  The other AttributeIDs' AnalogValues may also change back and forth depending on other conditions.      Desired Output  Room Name          AttributeID     AnalogValue     LogTimeStamp Conference Room    ONLINE_STATUS   0               2016-06-11 21:21:25:123 Gymnasium          ONLINE_STATUS   1               2016-07-21 20:23:45:456 Boardroom          ONLINE_STATUS   2               2017-02-17 05:15:37:951 Great Hall         ONLINE_STATUS   0               2016-10-23 07:28:54:753 ...