How to have select distinct of all but last column , and last column as comma separated values
I have a table with following structure. It has 8 columns where 1st 7 some time contain duplicate and last one can have different values (no unique though).
How do I select distinct for 1st 7 columns and then show the last column as comma separated values.
So the last column should look like following,
Select the distinct 7 values in a sub-query and then perform the XML STUFF to consolidate the values
Please note the "--Add your other fields here"
Select A.* ,CollectionDate = Stuff((Select Distinct ',' +cast(CollectionDate as varchar(max)) From YourTable Where Quantity=A.Quantity and Protein =A.Protein and Carb =A.Carb -- Add your other fields here For XML Path ('')),1,1,'') From (Select Distinct Quantity ,Protein ,Carb -- Add your other fields here From YourTable ) A
See also questions close to this topic
Oracle, Connect By rownum
I tried to find some informations about connect by "engine". I found this post: Confusion with Oracle CONNECT BY
User krokodilko answered and says:
The analyze of the last query: select level from dual connect by rownum<10; I leave to you as a homework assignment.
So i tried to do exactly as described to query
Select rownum from dual connect by rownum < 3
And here's my "work":
CREATE TABLE step1 AS SELECT 1 "LEVEL" FROM dual; SELECT * FROM step1; create table step2 as SELECT 2 "LEVEL" from dual JOIN step1 "PRIOR" on rownum <=3; SELECT * FROM step2; create table step3 as select 3 "LEVEL" from dual join step2 "PRIOR" on rownum <=3; SELECT * FROM step3; create table step4 as select 4 "LEVEL" from dual join step3 "PRIOR" on rownum <=3; SELECT * FROM step4;
But last SELECT still returns rows. Am I misunderstood something? Every time i Select LEVEL + 1 "LEVEL" it has rownum = 1 so it's always true. So am i failed steps?
python sqlite SELECT combine AND with OR
example I would like to select items with food in their 'title' or 'topic' that are created by '1'
here's my current code:
def find_note(self, value, user_id): self.cur.execute("""SELECT id, title, topic, content, created_at, modified_at FROM noteTbl WHERE title = ? OR topic = ? AND created_by = ?""",(value, value, user_id)) result = self.cur.fetchall() return result
and then after that I printed it:`
x = NoteDb() for row in x.find_note('food',1): print(row)
and I get blank.
sorry for my bad english
SQL - Selecting items from table based on data in other tables?
In the above relational schema, how would I do the following?:
List the names of all students that have a higher GPA than the minimum required GPA for the major they have applied for.
'In' operator comparison - SQL
I have below test data:
I have a variable
@var int = '5'and I want to see if
@varexists in one of the
'data'row in the table, I should report
pid,true. So is it possible to do
@varin (data....) ? That is can I find
'in'condition in the table data column?
How to find if a list of table names exist in one single stored procedure?
I have a temp table with 2 columns, one that lists all the table names in the database and the other columns to show if it exists in one stored procedure or not. I want to loop the list of table names inside the stored procedure and see if they exist or not. If it exists, I will update the second column in the temp table with 'Yes'.
Is it something that is possible?
I am trying to write the logic, but I am having a hard time forming it. Can someone please point me to the right direction?
This is what I have so far, but when I run it, the results show that none of the table names in the list exist in the stored procedure when that's not true.
DECLARE @TableName VARCHAR(50) CREATE TABLE #TablesList ( TableName VARCHAR(50), TableExists VARCHAR(3) ) INSERT INTO #TablesList (TableName) SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG='DB_Name' ORDER BY TABLE_NAME DECLARE TableNames_Cursor Insensitive CURSOR FOR SELECT TableName FROM #TablesList OPEN TableNames_Cursor FETCH NEXT FROM TableNames_Cursor INTO @TableName -- read the cursor rows, and process accordingly ----------------------------------------- WHILE (@@Fetch_Status) = 0 BEGIN --Update #TablesList --Set TableExists = 'Yes' -- where SELECT obj.TableName FROM (SELECT tl.TableName FROM sysobjects obj JOIN syscomments com ON com.id = obj.id JOIN #TablesList tl ON tl.TableName = @TableName WHERE obj.name = 'sProc_Name' AND com.text LIKE '%' + @TableName + '%') obj ORDER BY obj.TableName FETCH NEXT FROM TableNames_Cursor INTO @TableName END -- WHILE (@@Fetch_Status) = 0 DEALLOCATE TableNames_Cursor DROP TABLE #TablesList
How to get Id of a dapper insert query
I have a dapper query as a string like
insert into MyTable(Property1,Property2,Property3) values (Value1,Value2,Value3)
I have no properties separately or a model. All what I have is the query string. I run the query with dapper. After that, I would like to get Id of my insert. how can I do this?
SQL COALESCE and IS NULL are not returning a space when when the query returns NULL
I am trying to optimize a humongous SQL query that was written by a self taught developer that used a ton of functions instead of JOINS. Anyway, I am having trouble displaying a space or a empty string('') when there is no value in the field selected. I've included only the SELECT in question. I am having the weirdest problem or just overlooking the correct answer in troubleshooting. Whenever I use COALESCE, when the field is supposed to be a blank string, it displays a zero. And when I use IS NULL, I get back NULL. All info online seems to point toward using COALESCE(value, '') as depicted in the code. But I am getting a 0 instead of ''. Does anyone see what I'm doing wrong? I'm using SSMS.
SELECT pss8.dbo.xml_StripIllegalChars(dbo.rpt_get_series_volume(b.bookkey)) AS p_seriesvol --SELECT to be replaced that works but is slow due to function use I am told ,COALESCE(bd.seriesvolume, '') AS p_seriesvol --my SELECT that won't work! FROM bookdetail bd WHERE --bd.bookkey='303177' bd.bookkey='6002'
The bookkeys at the bottom are for testing as I know the top one returns a 1 and the bottom one returns a '' previously when it worked. The SELECT above my commented SELECT is the code that works but is slow... According to what I read online, I am saying 'if there isn't a series volume number, then it equals an empty string.' Does COALESCE not work like this? Can it only return a 0 if the field has no value, or in this case, has no volume number? All help much appreciated. I'm very curious to hear a solution!
Here's more intel. This is how the this SELECT works:
pss8.dbo.xml_StripIllegalChars(dbo.rpt_get_series_volume(b.bookkey)) AS p_seriesvol
function manages to create an empty string with this code... Does this reveal anything?
DECLARE @RETURN VARCHAR(5) DECLARE @v_desc VARCHAR(5) DECLARE @i_volumenumber INT SELECT @i_volumenumber = volumenumber FROM bookdetail WHERE bookkey = @i_bookkey and volumenumber <> 0 IF @i_volumenumber > 0 BEGIN SELECT @RETURN = CAST(@i_volumenumber as varchar(5)) END ELSE BEGIN SELECT @RETURN = '' END RETURN @RETURN END
Coalesce returning wrong value after a function call followed by multiplication
I have a report that presents information and I'm getting inconsistent information based on what appears to be some issue with a SQL view or possibly a SQL Function nested within the view. I've tried finding a way to debug the SQL View, however, it looks like SSMS only will debug Stored Procedures, so I'm not really sure how to step through and see what is happening. It really has me stumped and I can't help but wonder if it isn't a rounding issue. GetItemAverageCost
RETURNS DECIMAL(12,2)and the DataType in sitli.QuantityIssuedAtStockUOM is System.Int64 / bigint (sidenote: I'm confused about why LINQPad shows 2 data types for that column. In the tree on the left, after expanding the sitli table and hovering over the QuantityIssuedAtStockUOM the balloon BigInt NOT NULL pops up, but when I Take(100) and hover over the column in the result set it says System.Int64). Anyroad, here is the COALESCE function.
I'm not sure what else to include, but I haven't found many resources online that offer insight into this kind of a situation. Many thanks in advance for your time.
Deadlock issue in package/update query for concurrent access
I am using oracle package for one of the reports in out project. When this package accessed concurrently, below query is causing deadlock. Please suggest anyway to improve this query:
UPDATE temp_final t SET notnullcol = ( select COALESCE(h1,h2,h3,h4,h5,h6,h7,h8,h9,h10,h11,h12,h13,h14,h15,h16,h17,h18,h19,h20,h21,h22,h23,h23 ) from temp_final where ( ( rowgroup = 'G' and mid = t.seid) or ( rgroup = 'K' and t.rgroup = rgroup and t.mid = mid ) or ( rgroup = 'G' and t.rgroup = rgroup and t.mid = mid ) ) and rid = t.rid and rtype = t.rtype and sid = t.sid and pid = t.pid AND rid = 9 AND rtype =1 AND sid = 'value' AND pid = 421 ) --5,034 rows updated.
Please note, temp_final is a temp table and all columns h* are used in calculation so schema of this table can not be used.