What's wrong with this Dynamic Pivot table query? I'm staring myself blind on this.

mysql> SET @sql = NULL;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT
    ->   GROUP_CONCAT(DISTINCT
    ->     CONCAT(
    ->       'IF(q.label = ''',
    ->       label,
    ->       ''', 1, 2) AS ',
    ->       label
    ->    )
    ->   ) INTO @sql
    -> FROM question;
Query OK, 1 row affected (0.00 sec)

mysql> SET @sql = CONCAT('SELECT a.id, ', @sql, ', q.id
    '>       FROM answer a
    '>       INNER JOIN question q ON questionId = q.id
    '>       GROUP BY a.id');
Query OK, 0 rows affected (0.01 sec)

mysql> PREPARE stmt FROM @sql;
ERROR 1064 (42000): You have an error in your SQL syntax ... near ' q.id

Why does the first one pass but the second one blows up? Adding PREPARE stmt FROM @sql; on the GROUP_CONCAT query gives me this: You have an error ... near 'IF(q.label = 'Q1', 1, 2) AS Q1,IF(q.label = 'Q2', 1, 2) AS Q2,IF(q.label = '', 1' at line 1 I'm not expecting the label to be empty on the last if, but I don't see how that would blow up the last query. The query without the dynamic part returns this:

+----+-------+----+
| id | label | id |
+----+-------+----+
|  1 | Q1    |  1 |
|  2 | Q2    |  1 |
|  3 | Q1    |  1 |
|  4 | Q2    |  1 |
+----+-------+----+

I am looking for some feedback regarding the best way to handle the following (simplified for this example) situation. I have 3 tables: tasks, assignees, and users ---tasks---- id, task, completed ---assignees---- user_id, task_id ---users--- id, name I need to select tasks with concatenated user names of assignees, like so tasks.task | assigned_names.names --------------------------------------- "My task" | "Joe Schmoe, Bob Mcbart, Sally McGuire" Here is what I have currently SELECT tasks.task, assigned_names.names FROM tasks LEFT JOIN ( SELECT GROUP_CONCAT( users.name SEPARATOR ',' ) AS names, assignees.task_id FROM assignees LEFT JOIN users ON users.id = assignees.user_id GROUP BY assignees.task_id ) assigned_names ON assigned_names.task_id = tasks.id WHERE tasks.completed IS NULL My main concern is that the assignees table has hundreds of thousands of records, and doing a group on the whole table every time seems like not a good idea. Is there a better strategy? Also, I would be happy to rephrase my question title if someone could suggest better language. Thanks!

I'm trying to concatenate some columns and I would like to use space as my output string delimiter. Basically concat_ws would be a perfect fit, but it doesn't exist for SQLite3. I'm inside a SELECT already and I'd like to end up with a string that looks like this: col1_val col2_val col3_val I have col1, col2, and col3 as my columns, and they may have empty strings as values which I'd not want to delimit. col1, is safely expected to exist, to not be NULL, and not be empty. However either col2 and col3 might be empty, and I'd thus want to skip. I've tried a few different ideas, but all of them seem to end up with double delimiters when a string is empty. So some expected results: col1_val col1_val col2_val col1_val col3_val col1_val col2_val col3_val

I have always thought that CONCAT function is actually a wrapper over the + (String Concatenation) with some additional checks in order to make our life more easier. I have not found any internal details about how the functions are implemented. As to the performance, it seems that there is overhead for calling CONCAT when data is concatenating in a loop (which seems normal as there are additional handles for NULLs). Few days ago, a dev modified some string concatenation code (from + to CONCAT) because did not like the syntax and told me it became faster. In order to check the case, I have used the following code: DECLARE @V1 NVARCHAR(MAX) ,@V2 NVARCHAR(MAX) ,@V3 NVARCHAR(MAX); DECLARE @R NVARCHAR(MAX); SELECT @V1 = REPLICATE(CAST('V1' AS NVARCHAR(MAX)), 50000000) ,@V2 = REPLICATE(CAST('V2' AS NVARCHAR(MAX)), 50000000) ,@V3 = REPLICATE(CAST('V3' AS NVARCHAR(MAX)), 50000000); where this is variant one: SELECT @R = CAST('' AS NVARCHAR(MAX)) + '{some small text}' + ISNULL(@V1, '{}') + ISNULL(@V2, '{}') + ISNULL(@V3, '{}'); SELECT LEN(@R); -- 1200000017 and this is variant two: SELECT @R = CONCAT('{some small text}',ISNULL(@V1, '{}'), ISNULL(@V2, '{}'), ISNULL(@V3, '{}')) SELECT LEN(@R); -- 1200000017 For smaller strings, there is no differences. At some point, the CONCAT variant becomes faster: I wonder if someone can share any internals or explains the behavior as it seems that there might be a rule that it's better to concatenate large strings using CONCAT. Version: *Microsoft SQL Server 2022 (RTM-CU8) (KB5029666) - 16.0.4075.1 (X64) Aug 23 2023 14:04:50 Copyright (C) 2022 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: ) (Hypervisor)* ---------- The exact script looks like the following: DECLARE @V1 NVARCHAR(MAX) ,@V2 NVARCHAR(MAX) ,@V3 NVARCHAR(MAX); DECLARE @R NVARCHAR(MAX); SELECT @V1 = REPLICATE(CAST('V1' AS NVARCHAR(MAX)), 50000000) ,@V2 = REPLICATE(CAST('V2' AS NVARCHAR(MAX)), 50000000) ,@V3 = REPLICATE(CAST('V3' AS NVARCHAR(MAX)), 50000000); --SELECT @R = CAST('' AS NVARCHAR(MAX)) + '{some small text}' + ISNULL(@V1, '{}') + ISNULL(@V2, '{}') + ISNULL(@V3, '{}'); -- 00:00:45 -- 00:01:22 -- 00:01:20 --SELECT LEN(@R); -- 300000017 SELECT @R = CONCAT('{some small text}',ISNULL(@V1, '{}'), ISNULL(@V2, '{}'), ISNULL(@V3, '{}')) -- 00:00:11 -- 00:00:16 -- 00:00:10 SELECT LEN(@R); -- 300000017 where I am changing the last argument of the REPLICATE functions in order to generate larger strings for the concatenation. Then, I am executing each variant three times.

I need help using addition. When I do it populates using the concatenate function. I used the following in my access database Total: [ANTI TERROR]+[Records] and instead of returning 1 + 1 = 2 it returns 11 - i.e. it's using the concatenate function.

I was reading about the CONCAT() function, and I noticed this:

mysql> SELECT CONCAT(14.3);
        -> '14.3'

While running this command in MySQL 8.0 Command Line Client, the output didn't have quotes. So I wanted to verify the data type of the output (I assume it is a string data type). Please help me find the data type of the output.

I am trying to return a text field in a PostgreSQL query that is of the form 'stringOne' || string_agg(field, ',') || 'stringTwo' where for certain elements in the group clause, field is always null. I want, and expect, to end up with stringOnestringTwo in that case, but instead I get NULL. Why is this, and how do I accomplish what I'm trying to do? # Example Suppose I have the tables foo bar +----+--------+ +----+-------+--------------+ | id | name | | id | fooid | baz | +----+--------+ +----+-------+--------------+ | 1 | FooOne | | 1 | 1 | FooOneBazOne | | 2 | FooTwo | | 2 | 1 | FooTwoBazTwo | +----+--------+ +----+-------+--------------+ and I run the query SELECT foo.name AS foo, 'Bazzes: ' || string_agg(bar.baz, ', ') AS bazzes FROM foo LEFT JOIN bar ON bar.fooid = foo.id GROUP BY foo.name Then I want (and expect) to get the resultset +--------+------------------------------------+ | foo | bazzes | +--------+------------------------------------+ | FooOne | Bazzes: FooOneBazOne, FooOneBazTwo | | FooTwo | Bazzes: | <== NOT NULL +--------+------------------------------------+ but instead, the second row is ('FooTwo', NULL). How can I modify this query so that the second row returns ('FooTwo', 'Bazzes: ')?

When I try to concatenate two columns, of different type, I get the following warning: > Type conversion in the expression > (CONVERT_IMPLICIT(varchar(41), [TABLE].[COLUMN], 0)) can > affect "CardinalityEstimate" in choosing query plan. By doing a search... I seem to have understood that this warning can derive from the comparison of two columns of different types. For this reason I wanted to carry out some simple tests to deepen the thing:

SQL SERVER
CREATE TABLE TABLE_A (
	ID DECIMAL(5, 0) NOT NULL,
	COLUMN_A VARCHAR(255) NULL
)

INSERT TABLE_A (ID, COLUMN_A) VALUES (1, 'TEST_1')
INSERT TABLE_A (ID, COLUMN_A) VALUES (2, 'TEST_2')
INSERT TABLE_A (ID, COLUMN_A) VALUES (3, 'TEST_3')

Trying to concatenate with the operator, two columns of different types, an error is returned: "Error converting data type from varchar to numeric."

SQL SERVER
SELECT ID + COLUMN_A
FROM TABLE_A

If I try to do the same thing but converting the "ID" column, I no longer have the error but I get the previously mentioned warning.

SQL SERVER
SELECT CAST(ID AS VARCHAR(255)) + COLUMN_A
FROM TABLE_A

I have the same result if I try to concatenate using the concat() function, no error but I have the warning.

SQL SERVER
SELECT CONCAT(ID, COLUMN_A)
FROM TABLE_A

My question is this, how is it possible that I get a warning about comparing two columns even when they should be equal when I cast? How can I concatenate two columns of different types without that warning being returned? Thanks in advance, Giacomo.

I'm looking for a way to write a recursive query, but I'm getting an error. "SQL Error : ERROR: syntax error at or near "with recursive" Position: 287" query result "select * from tbl1" this is a list of numbers: 110,111,112... Each number must be inserted into a row "s_table_sd_||r.cut_name||_db_tmp.e_pack_log" i use: with tbl1 as ( select schema_name ,rtrim( ltrim( schema_name,'s_table_sd_'),'_db_tmp') as cut_name from information_schema.schemata s where schema_name like '%db_tmp' and rtrim( ltrim( schema_name,'s_table_sd_'),'_db_tmp') '' ) with recursive r as ( select cut_name from tbl1 union select * FROM s_table_sd_||r.cut_name||_db_tmp.e_pack_log where s_table_sd_||r.cut_name||_db_tmp.e_pack_log like '%'||r.cut_name||'%' ) select * from r Any ideas how to fix the request?

I have part of a string that I would like to remove when selecting. The Problem is that there are a few pieces within the string that changes. This is what I have so far. Select GROUP_CONCAT(DISTINCT Substr(Concat(Round(ms_cart.cart_qty), 'x', ms_cart.cart_sku), 1, 12), Concat(' | ', ms_cart_options.co_opt_name), Replace(ms_cart_options.co_opt_name, '

This is a buddy package. Who will the buddy photo be taken with

: THIS PART CHANGES

What team?: THIS PART CHANGES', '')) as Items FROM ms_cart INNER JOIN ms_orders ON ms_orders.order_id = ms_cart.cart_order INNER JOIN ms_cart_options ON ms_cart_options.co_cart_id=ms_cart.cart_id

Postgresql-11 select tick_time, nano_secs, concat( to_char(tick_time, 'MMDD HH24:MI:SS.US'), to_char(nano_secs, '000') ) from ticks order by tick_time, nano_secs limit 100; I want to concat 2 strings like above, but it always add a space char between the two strings. How to prevent it doing that? Thanks!

I have a query for producing some text for creating some VALUES text to put in a .sql file for inserting some rows. I get a blank line in the results: postgres=# SELECT ' (' || obj_id || ', ''' || obj_type || '''),' FROM il2.objects WHERE obj_id < 11 ORDER BY obj_id; ?column? ------------------------- (1, 'ScienceDomain'), (3, 'PIs'), (10, 'Instrument'), (4 rows) Doing a select *, it's pretty clear it's being caused by the obj_type being NULL for obj_id 2: postgres=# SELECT * FROM il2.objects WHERE obj_id < 11; obj_id | obj_type --------+--------------- 10 | Instrument 1 | ScienceDomain 2 | 3 | PIs (4 rows) (confirming it's NULL): postgres=# SELECT * FROM il2.objects WHERE obj_type IS NULL; obj_id | obj_type --------+---------- 2 | Why is the result of the first SELECT giving me a blank row? Even casting obj_type::text still gave me a blank row. ---------- Additional Info: The schema, for what it's worth: postgres=# \d il2.objects Table "il2.objects" Column | Type | Collation | Nullable | Default ----------+-------------------+-----------+----------+---------------------------------- obj_id | integer | | not null | generated by default as identity obj_type | character varying | | | Indexes: "objects_pkey" PRIMARY KEY, btree (obj_id)

I need to write a select statement that will replace a %S string in the FeatureString column with the actual values stored in the same table for a given PartID. With the following example data: +--------+--------------+---------------+------------------------+ | PartID | FeatureName | FeatureValue | FeatureString | +--------+--------------+---------------+------------------------+ | 1211 | AC | 5V | AC(%S)Boil(%S)Temp(%S) | | 1211 | Boil | 10v | AC(%S)Boil(%S)Temp(%S) | | 1211 | Temp | 5V | AC(%S)Boil(%S)Temp(%S) | +--------+--------------+---------------+------------------------+ I would like to retrieve the following FeatureValueString: +--------+-------------------------+ | PartID | FeatureName | +--------+-------------------------+ | 1211 | AC(5V)Boil(10v)Temp(5V) | +--------+-------------------------+ ### Explanation I need to replace the %S part of the FeatureString with the values that are stored in the corresponding FeatureName - FeatureValue column combinations. The last PartID with the value 7791 is a bit of a special case, as it only requires two of the values stored in the table. These being AC and Boil. The value for Temp isn't required in the FeatureString returned. ### Sample Data create table #partsfeature ( PartId int, FeatureName varchar(300), FeatureValue varchar(300), FeatureString varchar(300) ) insert into #partsfeature(PartId,FeatureName,FeatureValue,FeatureString) values (1211,'AC','5V','AC(%S)Boil(%S)Temp(%S)'), (1211,'Boil','10v','AC(%S)Boil(%S)Temp(%S)'), (1211,'Temp','5V','AC(%S)Boil(%S)Temp(%S)'), (2421,'grail','51V','Alc(%S)Coil(%S)grail(%S)'), (2421,'Coil','9V','Alc(%S)Coil(%S)grail(%S)'), (2421,'Alc','5V','Alc(%S)Coil(%S)grail(%S)'), (6211,'compress','33v','compress(%S)heat(%S)push(%S)'), (6211,'heat','90v','compress(%S)heat(%S)push(%S)'), (6211,'push','80v','compress(%S)heat(%S)push(%S)'), (5442,'compress','33v','compress(%S)heat()push(%S)'), (5442,'heat','90v','compress(%S)heat()push(%S)'), (5442,'push','80v','compress(%S)heat()push(%S)'), (7791,'AC','5V','AC(%S)Boil(%S)'), (7791,'Boil','10v','AC(%S)Boil(%S)'), (7791,'Temp','5V','AC(%S)Boil(%S)'), (8321,'Angit','50V','Angit(%S)Fan(%S)Hot(%S),Wether(%S)'), (8321,'Fan','9v','Angit(%S)Fan(%S)Hot(%S),Wether(%S)'), (8321,'Hot','3V','Angit(%S)Fan(%S)Hot(%S),Wether(%S)'), (8321,'Wether','12V','Angit(%S)Fan(%S)Hot(%S),Wether(%S)') ### Screenshot of Expected Results There can be more than three features.

i want to relate child to parent from same table. Table is built like this. id, parentid, name i want to have the complete path of the [name] columns without specify levels like i have in the example. I would like to have a better way to display path with kind of infinite levels of parent and child. i hope this make sense this is what i have SELECT case when s6.id is null and s5.id is null and s4.id is null and s3.id is null and s2.id is null then s1.name when s6.id is null and s5.id is null and s4.id is null and s3.id is null then s2.name || ' > ' || s1.name when s6.id is null and s5.id is null and s4.id is null then s3.name || ' > ' || s2.name || ' > ' || s1.name when s6.id is null and s5.id is null then s4.name || ' > ' || s3.name || ' > ' || s2.name || ' > ' || s1.name when s6.id is null then s5.name || ' > ' || s4.name || ' > ' || s3.name || ' > ' || s2.name || ' > ' || s1.name else 'n/a' end as path FROM mytable s1 LEFT JOIN mytable s2 ON s1.parentid = s2.id LEFT JOIN mytable s3 ON s2.parentid = s3.id LEFT JOIN mytable s4 ON s3.parentid = s4.id LEFT JOIN mytable s5 ON s4.parentid = s5.id LEFT JOIN mytable s6 ON s5.parentid = s6.id ; thanks in advance

I have a table with a column that is TEXT type. In the column are numeric characters. What I'm trying to achieve is to wrap those characters in double quotes. EXAMPLE: NAME ID QTY Apples A1 1 Oranges O1 1 Foo F1 0 IDEAL OUTPUT: NAME ID QTY Apples A1 "1" Oranges O1 "1" Foo F1 "0" I attempted to run the following SELECT statement but it didn't give me the result I was expecting. Perhaps you can guide me in the right direction? SELECT qty, CHAR('"'|| qty ||'"') FROM myTable; Thank You

I'm fairly new to SQL and I am am used to writing formulas in Crystal Reports. I want to recreate the following formula from Crystal Reports into an SQL statement to create a column:

if (not isnull ({OT.TITLE})) and (not isnull ({OA.LASTNAME}))then {OA.LASTNAME}& ", "& {OT.TITLE} else
if (not isnull ({OT.TITLE})) and (isnull ({OA.LASTNAME}))then {OT.TITLE} else ""

I created the following statement, but it only works if the title and last name fields are both filled out. Otherwise there is an unnecessary ,:

SELECT concat(OA.Lastname, ', ', OT.Title)
FROM OA
LEFT JOIN OT 
    ON OT.ObjectID = OA.ObjectID

Basically I need a column that shows the Last name and title separated by a comma. If there is no Last name, then show the title. If there is no title then show nothing, even if there is a last name. I would really appreciate any help you can give me. I am unfortunately working with SQL 2014.

I get the ids of a query by GROUP_CONCAT. SELECT GROUP_CONCAT(ArticleID) FROM TagMap WHERE Tag IN(...) How can I merge this query with a COUNT(*) group by SELECT ArticleID,COUNT(*) FROM TagMap WHERE Tag IN(...) GROUP BY ArticleID to get GROUP_CONCAT(ArticleID) with order of COUNT(*)? In other words, in the first query, I want to make GROUP_CONCAT(ArticleID) order by the number of tags found for each ArticleID.

My table has two column that is id and name. I want to do concatenate the columns if the if more than two names are same like: id name 1 John 2 David 3 Zed 4 John 5 David 6 John 7 David 8 John 9 John Now I want my output that if name is repeated than the first record will remain as it is and other will be concatenated by id like Id Name 1 John 2 David 3 Zed 4 4 John 5 5 David 6 6 John 7 7 David 8 8 John 9 9 John Can anyone please help...Thank you

I have the following code : start transaction; show datestyle; set datestyle to DMY; show datestyle; select concat(timestamp_field, '') from my_table ; commit; It outputs this : DateStyle ----------- ISO, DMY (1 ligne) DateStyle (after set datestyle to DMY;) ----------- ISO, DMY (1 ligne) select concat(timestamp_field, '') from my_table ; concat --------------------- 2004-09-01 00:00:00 (1 lignes) My datestyle change doesn't seem to be accepted. Moreover, concat keeps using ISO to format my timestamp_field. I would expect something like this : concat --------------------- 01/09/2004 00:00:00 (1 lignes) >*NOTA:* I don't want to use to_char function here. I want to understand how concat automagically converts a timestamp/date field.

I have this code: DECLARE @MyTable AS TABLE ( [Month] INT, Salary INT ); INSERT INTO @MyTable VALUES (1,2000), (1,3100); SELECT [Month], Salary FROM @MyTable; Output: I want to concat the Salary (grouping by month) so that it will be NVARCHAR like this: '2000,3100' How would I do this efficiently?