sql,linq,lambda,substring,for-xml-path , What is the equivalent of XML PATH and Stuff in Linq lambda expression (GROUP_CONCAT)?


What is the equivalent of XML PATH and Stuff in Linq lambda expression (GROUP_CONCAT)?

Question:

Tag: sql,linq,lambda,substring,for-xml-path

I am having a table like this :

EmployeeId  EmployeeName ItemName
4           Ganesh       Key Board
4           Ganesh       Processor
1           Jignesh      Key Board
1           Jignesh      Mouse
1           Jignesh      Processor
3           Rakesh       Key Board
2           Tejas        Key Board
2           Tejas        Mouse
2           Tejas        Processor

I need to query this as if the itemname is different for the same employeeid and employeename we should have the items as ',' separated.

Like the one which is given below :

EmployeeId  EmployeeName ItemName
1           Jignesh      Key Board, Mouse, Processor
2           Tejas        Key Board, Mouse, Processor
3           Rakesh       Key Board
4           Ganesh       Key Board, Processor

Here is the SQL Query for this: OPs Screen Scrape of STUFF hack which I couldn't OCR

Could anyone help me to convert the above SQL Query into Lambda Expression?


Answer:

I'm assuming of course by Lambda expression you mean a Linq statement (e.g. to EF or Linq2Sql).

The FOR XML PATH and STUFF example shown is a hack to workaround the lack of GROUP_CONCAT or LISTAGG in Sql Server.

You don't need this at all in LINQ - simply load all rows for the set of employees of interest into memory, and then use String.Join

var result = employeeItems
      .GroupBy(e => e.EmployeeId)
      .Select(eg => new 
       {
          EmployeeId = eg.Key,
          eg.First().EmployeeName,
          Items = string.Join(",", eg.Select(i => i.ItemName))
       });

Where employeeItems is a projection of the join between Employee and Items:

var employeeItems = new []
{
   new EmployeeItem{EmployeeId = 1, EmployeeName = "Ganesh", ItemName = "Keyboard"},
   new EmployeeItem{EmployeeId = 1, EmployeeName = "Ganesh", ItemName = "Mouse"},
   new EmployeeItem{EmployeeId = 2, EmployeeName = "John", ItemName = "Keyboard"}
};

Result:

1 Ganesh Keyboard,Mouse
2 John   Keyboard

Related:


Pull information from SQL database and getting login errors


php,sql,database
I am creating a very small, simple CRM for a company, they require the function to be able to view the last 25 orders via the dashboard. The orders are added via a Order-add form within the CRM. When adding the following code to the CRM I get an error:...

Select count Columns group by No


sql,sql-server
Emp_No Emp_Shift Emp_Date 500 AL 1/5/2015 600 S 2/5/2015 600 H 3/5/2015 500 S 4/5/2015 500 AL 5/5/2015 600 AL 6/5/2015 I need help on this issue , HOW TO RETURN count in 3 Columns >> EX: Emp_No Count Al Count S Count H 500 2 1 0 600 1...

SQL Multiple LIKE Statements


sql,sql-server,tsql,variables,like
I'm currently working on a report that shows me all post codes covered by our sales team. Each team covers over 100 post codes. What i would like to do is create a report that brings back the clients within the post code. Currently my code looks like this. SELECT...

Matplotlib: Plot the result of an SQL query


python,sql,matplotlib,plot
from sqlalchemy import create_engine import _mssql from matplotlib import pyplot as plt engine = create_engine('mssql+pymssql://**:****@127.0.0.1:1433/AffectV_Test') connection = engine.connect() result = connection.execute('SELECT Campaign_id, SUM(Count) AS Total_Count FROM Impressions GROUP BY Campaign_id') for row in result: print row connection.close() The above code generates an array: (54ca686d0189607081dbda85', 4174469) (551c21150189601fb08b6b64', 182) (552391ee0189601fb08b6b73', 237304) (5469f3ec0189606b1b25bcc0',...

How to select next row after select in SQL Server?


sql,sql-server
I have this issue, I need your help: EmpNo Shift_Date Shift1 shift2 stamp_Date stamp_Time stamp_Type 426 2015-04-12 A 2015-04-12 10:09:00.000 I 426 2015-04-15 B C 2015-04-15 23:46:00.000 I 426 2015-04-15 B C 2015-04-15 23:45:00.000 O 426 2015-04-16 OF 2015-04-16 07:02:00.000 O 426 2015-04-17 A 2015-04-17 07:34:00.000 I 426 2015-04-18 A...

Inner Join 3 tables pl/sql


mysql,sql
Basically i want to inner join 3 tables and the case is understood by the query itself as follows. Tables: A has 2 columns column 1 and column 2 B has 2 columns column 3 and column 4 C has 3 columns column 5,column 6 and column 7 Query: select...

Get unique row by single column where duplicates exist


sql,sql-server
I have the following DB table called messages with columns: thread_id, sender_id, receiver_id, date Example: +-----------+------------+-------------+-------------+ | thread_id | sender_id | receiver_id | date | +----------------------+-----------+-----------------+ | 1 | 1 | 2 | 11/06/2015 | | 1 | 2 | 1 | 14/06/2015 | | 1 | 1 | 2...

orderby () containing numbers and letters


c#,wpf,linq,linq-to-sql,sql-order-by
I want to order an array containing numbers and letters in an ascending order the values are stored as a string in a DB. This is the list I have 4B 1A 1 1B 2 4C 4 and want them to order like this where numbers are ascending followed by...

SQL Group By multiple categories


php,mysql,sql,mysqli
This is the query I'm using to count how many events are booked for each category of bookings. SELECT Categories.name, count(case when locations.name ='loc 1' then 1 end) as Location1, count(case when locations.name ='loc 2' then 1 end) as Location2, count(case when locations.name ='loc 3' then 1 end) as Location3,...

SQL: overcoming no ORDER BY in nested query


sql,sqlite
I have a table t as follows: CREATE TABLE t( id INTEGER PRIMARY KEY AUTOINCREMENT, date TEXT, a TEXT, b TEXT ); Sample Data 1|2015-06-15|a1 15|b1 15 2|2015-06-15|a2 15|b2 15 3|2015-06-16|a1 16|b1 16 4|2015-06-16|a2 16|b2 16 5|2015-06-17|a1 17|b1 17 6|2015-06-17|a2 17|b2 17 I want to select all values of a...

Take thousand value in SQL


sql,sql-server
I have values like below I need to take only the thousand value in sql. 38,635.123 90,232.89 123,456.47888 I need to take result as below. 635 232 456...

mysql_real_escape_string creates \ in server only not in local


php,sql
When I use mysql_real_escape_string in my localhost and I output the result of html in a table I have no problem. But when I use it on my server it outputs even the \ This is how I use it: $_GETVARS['txtEmpNum'] = mysql_real_escape_string($_GETVARS['txtEmpNum']); $_GETVARS['txtLName'] = mysql_real_escape_string($_GETVARS['txtLName']); $_GETVARS['txtFName'] = mysql_real_escape_string($_GETVARS['txtFName']); $varSQL...

sqlite query slow, how to optimize (using linq to entities)


c#,linq,entity-framework,sqlite
I have used MS SQL server a few times and have yet to run into a speed issue when querying, using linq to entities. This time, I am using sqlite, as to ship the entire database with the application. I have a winforms app that contains 4 search fields. My...

Is there a better way to write this query involving a self select?


sql,postgresql,join,aggregate-functions
I am looking for a "better" way to perform a query in which I want to show a single player who he has played previously and the associated win-loss record for each such opponent. Here are the tables involved stripped down to essentials: create table player (player_id int, username text);...

Using Sum in If in Mysql


mysql,sql,select,sum
I want to select the sum of values of my_field. The unit of my_field is in seconds. I don't want to exceed 24 hours, so when it exceeds 24 hours, I want it to select 24*60*60 = 86400. SELECT IF(SUM(my_field) > 86400, 86400, SUM(my_field)) ... This solution doesn't seem to...

The column name “FirstName” specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument


sql,sql-server,sql-server-2008
I am getting the following error message when I am trying to do replace null to zero. The column name "jan" specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument. Query below: select * from(select isnull(jan,0),isnull(feb,0),sum(data) as amount )as p pivot( sum(amount) for month...

Select all none null values from all columns in a table


mysql,sql
I am having an issue going through column by column and getting a count of all the non-null values in each column. I think issue is how I am getting the column names. In the last select statement where I am trying to select all of the values in each...

Linq update with specific select of column being null


c#,linq
I wanted to select few columns from SQl and then update that table. I used var xyz_repl= from mfrepl in xyz_repl_alias where (mfrepl.RPT_ID == sched_data.NOTIF_RPT_ID) && (mfrepl.NOTIF_ID == "" || mfrepl.NOTIF_ID == null) select mfrepl; This did my job but then it does not work if any column is NULL.Then...

Foreign key in C#


c#,sql,sql-server,database
I have two tables, A and B, in a dataset in SQL Server; I have created a connection to the dataset in a c# project in visual studio. How can I create a foreign key ( A is the parent) between my two tables ? I want to create the...

Looping distinct values from one table through another without a join


sql,sql-server,tsql,while-loop
I know looping is not ideal in SQL, but I couldn't think of another way of doing this. I want each distinct row from this Table 1 to have each distinct date and hour produced on Table 2. In other words, Table 2 has the dates between 05/01/2014 through 04/30/2015,...

Query how often an event occurred at a given time


mysql,sql
[Aim] We would like to find out how often an event "A" ocurred before time "X". More concretely, given the dataset below we want to find out the count of the prior purchases. [Context] DMBS: MySQL 5.6 We have following dataset: user | date 1 | 2015-06-01 17:00:00 2 |...

Recursive Lag Column Calculation in SQL


sql,sql-server,recursion
I am trying to write a procedure that inserts calculated table data into another table. The problem I have is that I need each row's calculated column to be influenced by the result of the previous row's calculated column. I tried to lag the calculation itself but this does not...

Error when building an XDocument


c#,xml,linq,xpath,linq-to-xml
Using the following example xml containing one duplicate: <Persons> <Person> <PersonID>7506</PersonID> <Forename>K</Forename> <Surname>Seddon</Surname> <ChosenName /> <MiddleName /> <LegalSurname /> <Gender>Male</Gender> </Person> <Person> <PersonID>6914</PersonID> <Forename>Clark</Forename> <Surname>Kent</Surname> <ChosenName>Clark</ChosenName> <MiddleName />...

Add 1 to datediff result if time pass 14:30 or 2:30 PM


sql,ms-access,ms-access-2007
Here is my SQL query which returns number of days by subtracting current date from specified date and returns exact as i need, but in addition i want to add 1 to result if current time passes 14:30 or 2:30. My query SELECT reservations.customerid, DateDiff("d",reservations.checkin_date,Now()) AS Due_nights FROM reservations Am...

Convert AWK command to sqlite query


sql,awk,sqlite3
I have to parse very big (abt 40Gb) text files(logs) very often. Usually AWK/grep is enough for my needs, but logs are growing and now I'm curious whether sqlite3 will allow me to do the same things more efficiently. I chosen sqlite for my tests as it installed out-of-the-box (SQLite...

How to declare var datatype in public scope in c#?


c#,linq
I write simple query with linq to sql : var query = (from p in behzad.GAPERTitles select new { p.id, p.gaptitle }).ToArray(); up code into the c# windows application ,windows form load event,and i want use up result into the button click event in this scope: private void button1_Click(object sender,...

Load XML to list using LINQ [duplicate]


c#,xml,linq
This question already has an answer here: XDocument to List of object 1 answer I have following XML: <?xml version="1.0" encoding="utf-8"?> <start> <Current CurrentID="5"> <GeoLocations> <GeoLocation id="1" x="78492.61" y="-80973.03" z="-4403.297"/> <GeoLocation id="2" x="78323.57" y="-81994.98" z="-4385.707"/> <GeoLocation id="3" x="78250.57" y="-81994.98" z="-4385.707"/> </GeoLocations> <Vendors> <Vendor id = "1" x="123456" y="456789" z="0234324"/>...

timestamp SQL to Excel


php,mysql,sql,excel
If this is a duplicate, please let me know, I haven't found anything. I have written a php file that can read content from a database table and write it into a excel .xls file. Everything works fine except by that timestamps. In my generated .xls file every timestamp is...

SQL Repeated Condition in Two Tables


sql,variables,coding-style,condition
I am a beginner to SQL, and I am having trouble getting rid of repeated code. I have a condition "AccountId=20" that I use twice in a SQL query. The SQL looks something like: DELETE FROM Table_A WHERE FunctionId IN (Select FunctionId FROM Table_B WHERE AccountId=20); DELETE FROM Table_B WHERE...

SQL varchar variable inserts question mark


sql,sql-server-2012
I've a database value that when inserted into a SQL variable, shows with question mark at the end !! can't find a reason?! declare @A varchar(50) = 'R2300529‏' select @A Results: R2300529? any explanation? i'm using SQL server 2012....

How to join result of a Group by Query to another table


sql,tsql
I have the following Group By SQL: SELECT s.Login_Name, COUNT(s.s1CIDNumber) FROM [dbSuppHousing].[dbo].[tblSurvey] s group by s.Login_Name I want to know how I can join this result set to another table(tblUsers) to add user_id to the result set (tblSurvey and tblUsers have 1-1 relationship on Login_Name) I tried the following: Select...

Sort Array by contains specific char count


c#,linq
I have an array and I want to sort this array by its element' specific character count. var myNewArray = myArray.ToList().Sort(u => u.Name.Split(' ').Length); but this does not work at all. How can I provide the LINQ code for this problem ? myArray[0] = "word1 word2" myArray[1] = "word1" myArray[2]...

How can I create missing date records for each employee based off a limited calendar?


mysql,sql,tsql
I have two tables, one an abbreviated calendar: create table dbo.calendar ( [date] datetime ) insert into dbo.calendar values ('20150101 00:00:00 AM') -- 1/1/15 insert into dbo.calendar values ('20150102 00:00:00 AM') -- 1/2/15 insert into dbo.calendar values ('20150103 00:00:00 AM') -- 1/3/15 insert into dbo.calendar values ('20150104 00:00:00 AM') --...

Retrieve Values As Column


mysql,sql
I've the following table in my PHPMYADMIN The desired output is headline                              impressions              clicks Buy A new Iphone                 ...

Select Statement on Two different views


sql
I couldn't find the answer to my specific question anywhere. Is it possible to select from two different views? For example my code looks something like this right. select view1.col1, view1.col2, view1.col3 from dbo.view1 inner join ~~~~~ inner join ~~~~~ but I want to include a column from a different...

MySQL: Select several rows based on several keys on a given column


mysql,sql,database
I have this mysql table structure: ------------------------------------ | item_id | meta_key | meta_value | ------------------------------------ 159 category Bungalow 159 location Lagos 159 price 45000 160 category Bungalow 160 location Abuja 160 price 53500 ... 350 category Bungalow 350 location Lagos 350 price 32000 What I'd like to do is select...

Can someone explain to me how this statement is an exclude?


sql,sql-server,tsql
Can someone tell me how this is an exclude? Assuming that tableID is auto generated and in columnY there can be value of 0 or 1. This statement should exclude everything were columnY has a value of 1. SELECT * FROM [table].[dbo].[one] AS t1 LEFT JOIN [table].[dbo].[one] AS t2 ON...

Default the year based on month value


sql,sql-server
I have a query to display the year value from a table and default the current year. select distinct year(startdate) as syear, year(startdate) as eyear, 1 as sOrder from [TTS].[dbo].[Class] where year(startdate) <> year(getdate()) union all select year(getdate()) as syear, year(getdate()) as eyear, 0 as sOrder order by sOrder asc,...

left join table, find both null and match value


sql,sql-server,join
I have two 2 table t1(years int, numOfppl int), t2(years int, numOfppl int). t1 contains years between 2001 and 2010 , t2 contains years between 2003-2005 and 2007-2010 I want to query a result like that t1.years t1.numOfppl t2.yeras t2.numOfppl 2001 7 null null 2002 6 null null 2003 4...

Fastest way to add a grouping column which divides the result per 4 rows


sql,sql-server,tsql,sql-server-2012
If i have a resultset like this for example (just a list of numbers) : 1,2,3,4,5,6,7,8,9,10,11 and I would like to add a grouping column so i can group them per 4 like this : 1,1,1,1,2,2,2,2,3,3,3 (The last one in this examle does not have a forth element, so that...

how can I add a column to IQueryable object and modify its values


c#,.net,linq,grid,devexpress
var packs = from r in new XPQuery<Roll>(session) select new { Number = r.number Selection = new bool() }; gcPack.DataSource = packs; I want to add another column to my grid control with: Selection = new bool(). It will be added to the grid but I can't change its...

Purging Database - Count purged/not-purged tables


mysql,sql,sql-server,database,stored-procedures
So I am working with a database where I will be purging various tables that contain rows that are older than 30days. I have fairly limited knowledge of SQL and wanted to know if there was a certain way to select the row that will be purged and the rows...

SQL only one cell from column can have X value


mysql,sql
Is there a way to ensure, when you design a table - SQL, MySQL -, that only one cell from column X is true, the rest being automatically false? When you change the value of a different cell to true, all other cells should automatically become false. A radio button...

Update list of items in c#


c#,linq,list,updates
I would like to know if you can suggest me an efficient way to update a list of items in c#. Here is a generic example: If CurrentList is [ {Id: 154, Name: "George", Salary: 10 000} {Id: 233, Name: "Alice", Salary: 10 000}] And NewList is [ {Id: 154,...

for-loop add columns using SQL in MS Access


sql,ms-access,table,for-loop,iteration
I am trying to add n columns to a table, like in this example of code where n = 10: Sub toto() Dim db As Database, i As Integer Set db = CurrentDb() For i = 1 To i = 10 db.Execute " ALTER TABLE time_series " _ & "ADD...

oracle sql error Case When Then Else


sql,oracle,oracle11g
SELECT * FROM FirstTable WHERE RowProcessed = 'N' AND ( CASE WHEN EXISTS(SELECT top 1 FROM SecondTable) THEN 1 ELSE EXISTS( SELECT SecondTable.RowProcessed FROM SecondTable WHERE FirstTable.Key = SecondTable.Key AND SecondTable.RowProcessed = 'Y' ) END ) AND OtherConditions Case When then else in where clause. Not sure about the syntax....

SQL Customized search with special characters


sql,sql-server,sql-server-2008
I am creating a key-wording module where I want to search data using the comma separated words.And the search is categorized into comma , and minus -. I know a relational database engine is designed from the principle that a cell holds a single value and obeying to this rule...

Title search in SQL With replacement of noice words [on hold]


sql,sql-server,sql-server-2008
I have Two tables first us IMG_detail S.NO Title 1 women holding stack of gifts 2 Rear view of a man playing golf 3 Women holding gifts 4 Close-up of a golf ball on a tee 5 Businessman reading a newspaper and smiling and Second is tbl_NoiceWords SN Key 1...

How do I find records in one table that do exist in another but based on a date?


mysql,sql
I want to find all the dealers who haven't had an order in 2015 yet. I know this query doesn't work but I thought it might be helpful to understand what I want to do. In this example I want to get just "Bob" as a result. He is the...

T-SQL Ordering a Recursive Query - Parent/Child Structure


sql,tsql,recursion,order,hierarchy
I am trying (and failing) to correctly order my recursive CTE. My table consists of a parent-child structure where one task can relate to another on a variety of different levels. For example I could create a task (this is the parent), then create a sub-task from this and then...