Using a join operation with conditionals I have two tables that I'm working with in my database. I am using SQL Server 2014.


One is 'versions', that contains version_id, version_number,
start_date, end_date and server_id.
The other is 'features', which contains feature_id, feature_info and
version_id. version_id is a foreign key to the version_id in
'versions', where it is a PK.


I'm trying to get something like:

"X, Y, Z are in version 1.0.
"....... are in version 1.2.

and then if a version does not have a new feature:

"Version 2.2 has no new features."

Right now, I don't have any versions without features, but I plan on adding some to test this out.

Here is the output I'm currently getting with my code (I don't care about the singular/plural issues, i.e. 'Version 1.7 has email client'):

Version 1.0       has web functionality, Version 1.1       has user interface, Version 1.7       has email client, Version 1.7       has voice control functionality, Version 2.0       has bug fixes.

There happens to be 7 spaces in between commas. This is less important, but would still be nice to be able to fix.

*Also, if I wanted to turn this into a stored procedure, would I just add, 'create procedure x', followed by 'begin' and 'as' after my variable declaration, and then an 'end' at the end of the code?

DECLARE @result varchar(1000)

SET @result = ''

BEGIN TRY

    SELECT @result = @result + 'Version ' + version_number + 'has ' +      feature_info + ', ' 
    FROM features join versions on versions.version_id = features.version_id
    SELECT substring(@result, 0, len(@result) - 1)

END TRY

BEGIN CATCH
    PRINT 'THere was an error getting feature info'
END CATCH
You can make use of the FOR XML PATH() construct to create the list of features.

For instance:

USE tempdb;

IF OBJECT_ID('dbo.Features') IS NOT NULL
DROP TABLE dbo.Features;
IF OBJECT_ID('dbo.Versions') IS NOT NULL
DROP TABLE dbo.Versions;
GO
CREATE TABLE dbo.Versions
(
    VersionID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , VersionNumber DECIMAL(10,2) NOT NULL
    , StartDate DATETIME NOT NULL
    , EndDate DATETIME NULL
    , ServerID INT NOT NULL
);
CREATE TABLE dbo.Features
(
    FeatureID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , FeatureInfo VARCHAR(250) NOT NULL 
    , VersionID INT NOT NULL
        FOREIGN KEY REFERENCES dbo.Versions(VersionID)
);

INSERT INTO dbo.Versions(VersionNumber, StartDate, EndDate, ServerID)
VALUES (1.0,'2016-03-22T00:00:00', '2016-03-23T00:00:00', 1001)
    , (2.0,'2016-03-23T00:00:00', '2016-03-24T00:00:00', 1001)
    , (2.1,'2016-03-24T00:00:00', NULL, 1001);

INSERT INTO dbo.Features (FeatureInfo, VersionID)
VALUES ('blah', 1)
    , ('blee', 1)
    , ('bloh', 2);

;WITH Deets
AS
(
    SELECT VersionNumber = CONVERT(VARCHAR(10), v.VersionNumber, 0)
        , Features = STUFF((SELECT TOP(2147483647) ', ' + f.FeatureInfo
        FROM dbo.Features f
        WHERE f.VersionID = v.VersionID
        ORDER BY f.FeatureID
        FOR XML PATH ('')
        ), 1,2,'')
    FROM dbo.Versions v
)
SELECT 'Version ' + d.VersionNumber + ' has ' + 
    CASE WHEN d.Features IS NULL THEN 'no features' 
        ELSE d.Features 
    END
FROM Deets d;


This returns a nice list, like:



Notice several things from my code above:


I always specify the schema
I use upper case for the SQL keywords
I use Camel Case for column names
The values I specified for the date columns are in ISO format, which makes it easier for SQL Server to understand, and prevents errors.