How to Return Multiple Values in One Column (T-Sql)

How to return multiple values in one column (T-SQL)?

You can use a function with COALESCE.

CREATE FUNCTION [dbo].[GetAliasesById]
(
    @userID int
)
RETURNS varchar(max)
AS
BEGIN
    declare @output varchar(max)
    select @output = COALESCE(@output + ', ', '') + alias
    from UserAliases
    where userid = @userID

    return @output
END

GO

SELECT UserID, dbo.GetAliasesByID(UserID)
FROM UserAliases
GROUP BY UserID

GO

How to return multiple values as one field in SELECT statement

The way to do this in SQL Server is to use the STUFF function. To demonstrate how this is used, I give a simplified example below. This uses a self join, but it is easy to change.

declare @test1 table
(
fkfield int,
datafield varchar(50)
)

insert into @test1 VALUES
(1,'ABC'),  
(1,'DEF'),   
(2,'xyz'),   
(2,'mno'),
(2,'pqr')

SELECT DISTINCT 
       fkfield,
       Stuff((SELECT ', ' + datafield  
              FROM   @test1 t2 
              WHERE  t2.fkfield  = t1.fkfield 
              FOR XML PATH('')), 1, 2, '') ConCatData
FROM   @test1 t1  

The results look like this:

1   ABC, DEF
2   xyz, mno, pqr

HTH

How to return multiple values ​for multiple columns in a single conditional command - Pyspark or HQL

In pyspark, there are several complex types that you can use to do that.

Here is an example with array:

from pyspark.sql import functions as F

# Assuming your dataframe is called df
df.show()
+---+---+
|  A|  B|
+---+---+
| -1|  0|
|  0|  0|
|  1|  0|
+---+---+

df = df.withColumn(
    "test",
    F.when(F.col("A") == F.col("B"), F.array(*map(F.lit, [0, 1, 0])))
    .when(F.col("A") < F.col("B"), F.array(*map(F.lit, [0, 0, 1])))
    .when(F.col("A") > F.col("B"), F.array(*map(F.lit, [1, 0, 0]))),
)

df.show()
+---+---+---------+
|  A|  B|     test|
+---+---+---------+
| -1|  0|[0, 0, 1]|
|  0|  0|[0, 1, 0]|
|  1|  0|[1, 0, 0]|
+---+---+---------+

Once test column is created, you can assign it to other columns with getItem:

df = df.select(
    "A", "B", *(F.col("test").getItem(i).alias(f"col{i+1}") for i in range(3))
)

df.show()
+---+---+----+----+----+
|  A|  B|col1|col2|col3|
+---+---+----+----+----+
| -1|  0|   0|   0|   1|
|  0|  0|   0|   1|   0|
|  1|  0|   1|   0|   0|
+---+---+----+----+----+

Return multiple values from the same column in the same table

    select e.empname employeeName, m.empname managerName
from employees e , departments d,employees m 
where e.deptid=d.deptid and d.managerid=m.empid

How to return multiple values from XML element in SQL?

Please try the following solution.

As @Larnu pointed out, it is much better to shred the XML as rows.

If needed it is very easy to filter out names with NULL values.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, TargetData_Xml XML);
INSERT INTO @tbl (TargetData_Xml) VALUES
(N'               xmlns="http://schemas.datacontract.org/2004/07/TriTech.InformRMS.Domain.Core.ComplexTypes">
    
        ca2fa1dd-2cd4-c219-bea5-08d6fbe6d96c
        
        19bc33e1-a788-cd99-3dab-08d92aa7d030
        Case Number WB21-006637
        Case Management
    
    
        ca2fa1dd-2cd4-c219-bea5-08d6fbe6d96c
        
        cb4d829b-c31a-cadb-5c9e-08d934b7404d
        Incident Supplement Number WB21-006637.006
        Data Entry
    
    
        ca2fa1dd-2cd4-c219-bea5-08d6fbe6d96c
        
        6b23195c-4650-c0c9-925d-08d71a88f611
        
        Template
    
    
        ca2fa1dd-2cd4-c219-bea5-08d6fbe6d96c
        
        b465517c-5926-c6b3-1cc6-08d6fbe6da27
        Default Workflow
        Workflow
    
    
        
        
        
        Complete
        Workflow Step
    
');
-- DDL and sample data population, end

WITH XMLNAMESPACES (DEFAULT 'http://schemas.datacontract.org/2004/07/TriTech.InformRMS.Domain.Core.ComplexTypes')
SELECT ID
    , c.value('(Name/text())[1]', 'VARCHAR(100)') AS [Name]
FROM @tbl
    CROSS APPLY TargetData_Xml.nodes('/ArrayOfTarget/Target') AS t(c);

Output

+----+--------------------------------------------+
| ID |                    Name                    |
+----+--------------------------------------------+
|  1 | Case Number WB21-006637                    |
|  1 | Incident Supplement Number WB21-006637.006 |
|  1 | NULL                                       |
|  1 | Default Workflow                           |
|  1 | Complete                                   |
+----+--------------------------------------------+

How to return multiple values within a group of rows.

You're almost there. Yes, you need to use TOP 1, what's wrong with that?

Try this:

SELECT ID, Location, ConfigID
FROM myTable t1
WHERE ConfigID IS NULL
   OR ID = (SELECT TOP 1 ID
            FROM myTable t2
            WHERE t2.Location = t1.Location
            ORDER BY ConfigID DESC)

Here is a fiddle.

How to return multiple row value as a single row with multiple column using sql

You are close. You just need aggregation:

SELECT MARKS.StudentID,
       MAX(CASE WHEN SUBJECTS.SubjectCode = 'MATHS' THEN MARKS.MARK END) as Maths, 
       MAX(CASE WHEN SUBJECTS.SubjectCode = 'SCIENCE' THEN MARKS.MARK END) as Science, 
       MAX(CASE WHEN SUBJECTS.SubjectCode = 'ENGLISH' THEN MARKS.MARK END) as English, 
FROM MARKS JOIN
     SUBJECTS 
     ON SUBJECTS.SubjectID = MARKS.SubjectID
GROUP BY MARKS.StudentID
ORDER BY MARKS.StudentID;

I would recommend that you use table aliases, so the query is easier to write and read:

SELECT m.StudentID,
       MAX(CASE WHEN s.SubjectCode = 'MATHS' THEN M.MARK END) as Maths, 
       MAX(CASE WHEN s.SubjectCode = 'SCIENCE' THEN M.MARK END) as Science, 
       MAX(CASE WHEN s.SubjectCode = 'ENGLISH' THEN M.MARK END) as English, 
FROM MARKS m JOIN
     SUBJECTS s
     ON s.SubjectID = m.SubjectID
GROUP BY m.StudentID
ORDER BY m.StudentID;

---

Related Topics