SELECT type as result FROM tbl_users
Result:
admin
admin
user
admin
editor
SELECT STRING_AGG(type,',') as result FROM tbl_users
Result:
admin,admin,user,admin,editor
https://database.guide/the-sql-server-equivalent-to-group_concat/
MYSQL equivalent is GROUP_CONCAT()
SELECT tbl_a.*, tbl_b.* FROM tbl_a INNER JOIN tbl_b ON tbl_a.name = tbl_b.name OR ( tbl_a.name IS NULL AND tbl_b.name IS NULL )
Create and call function
CREATE FUNCTION dbo.PK_DistinctList ( @List VARCHAR(MAX), @Delim CHAR ) RETURNS VARCHAR(MAX) AS BEGIN DECLARE @ParsedList TABLE ( Item VARCHAR(MAX) ) DECLARE @list1 VARCHAR(MAX), @Pos INT, @rList VARCHAR(MAX) SET @list = LTRIM(RTRIM(@list)) + @Delim SET @pos = CHARINDEX(@delim, @list, 1) WHILE @pos > 0 BEGIN SET @list1 = LTRIM(RTRIM(LEFT(@list, @pos - 1))) IF @list1 <> '' INSERT INTO @ParsedList VALUES (CAST(@list1 AS VARCHAR(MAX))) SET @list = SUBSTRING(@list, @pos+1, LEN(@list)) SET @pos = CHARINDEX(@delim, @list, 1) END SELECT @rlist = COALESCE(@rlist+',','') + item FROM (SELECT DISTINCT Item FROM @ParsedList) t RETURN @rlist END
and then call:
SELECT dbo.PK_DistinctList('342,34,456,34,3454,456,aa,bb,cc,aa',',') as DistinctList
OR
SELECT dbo.PK_DistinctList(tbl_persons.phone_numbers,',') as DistinctPhones
Source: https://blog.sqlauthority.com/2009/01/15/sql-server-remove-duplicate-entry-from-comma-delimited-string-udf/
select nullif(name, '') from tbl_persons
Wrong:
SELECT CASE WHEN gender='1' THEN 'male' as gender_description, 'blue' as color_description ELSE 'female' as gender_description, 'red' as color_description END FROM tbl_persons
Right:
SELECT CASE WHEN gender='1' THEN 'male' ELSE 'female' END as gender_description, CASE WHEN gender='1' THEN 'blue' ELSE 'red' END as color_description FROM tbl_persons
UPDATE tbl_persons SET gender_description = CASE WHEN gender = '1' THEN 'male' WHEN gender = '2' THEN 'female' ELSE 'unknown' END
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tbl_test' AND TABLE_SCHEMA='mojaSchema'
Problem: u tablici imamo popis knjiga sa sljedećim podacima: book_id, book_year, book_name, books_sold, books_ordered.
Dakle, za svaku knjigu imamo za različite godine: isto ime i book_id ali različit broj naručenih i prodanih knjiga.
Zanima nas book_id, book_year, book_name, books_sold i books_ordered za zadnju godinu za svaku knjigu.
SELECT B.book_id, B.book_year, B.book_name, B.books_sold, B.books_ordered FROM tbl_books AS B INNER JOIN ( SELECT book_id, MAX(book_year) as max_year FROM tbl_books GROUP BY book_id ) as M ON B.book_id = M.book_id GROUP BY B.book_id, B.book_year, B.book_name, B.books_sold, B.books_ordered
Dakle, prvo uzimamo max year za svaku knjigu pomoću
SELECT book_id, MAX(book_year) as max_year FROM tbl_books GROUP BY book_id
a zatim popis knjiga joinamo sa dobivenim max vrijednostima.
SELECT * FROM tbl_boje ORDER BY CASE WHEN boja=0 THEN 'plavo' WHEN boja=1 THEN 'zeleno' WHEN boja=2 THEN 'crno' WHEN boja=3 THEN 'bijelo' ELSE 'nepoznato' END
SELECT tbl_a.*, dtbl_B.* FROM tbl_a inner join ( VALUES (1,1),(5,2),(4,3) ) as dtbl_B (id, poredak) on tbl_a.id = dtbl_B.id