Database

BEGIN TRAN
DELETE tblResultNotes FROM tblResultNotes 
INNER JOIN tblResults 
ON tblResultNotes.ResultID = tblResults.ResultID
ROLLBACK TRAN

CASE [ID_SECTION]
  WHEN 1 THEN 1
  WHEN 2 THEN 2
  WHEN 3 THEN [ID_Tarriff_Gender]
  ELSE 0
END

Hum Dum wrote:

Is it correct ?

Hum Dum wrote:

Any comments !!

Shameel wrote:

In this case, CASE is an overkill and an IF statement
works perfectly well.

Shameel wrote:

If you see the example, each nested CASE statement..,

jschell wrote:

The correct solution is to use it correctly.

SilimSayo wrote:

Most of our imports come from abroad!

nallelcm wrote:

here is my problem.

-- Just to be on the safe side
BEGIN TRANSACTION

-- The sample table
CREATE TABLE Employees
(
     id       INT
    ,[name]   VARCHAR(50)  
    ,position VARCHAR(50)
);

-- The sample data
INSERT INTO Employees (id, [name], position) 
      SELECT 1,    'bob',     'sales'
UNION SELECT 2,    'joe',     'sales'
UNION SELECT 3,    'mark',    'management'
UNION SELECT 4,    'jane',    'management'
UNION SELECT 5,    'julie',   'it'
UNION SELECT 6,    'donald',  'sales'

-- Making a list of unique positions
SELECT DISTINCT position 
           INTO #Positions 
           FROM Employees;

-- Select all positions, using a FOR XML subquery to fetch the members
-- The REPLACE and SUBSTRING functions are used to remove the XML-tags
SELECT position, 
       SUBSTRING(names, 0, LEN(names) - LEN(', '))
FROM   (SELECT position,
               REPLACE(REPLACE((SELECT [name]
                                FROM   employees e
                                WHERE  e.position = p.position
                                FOR XML AUTO), '<E name="', ''), '"/>', ', ') AS
               names
        FROM   #positions p) AS tmp_cte;  

-- Undo everything we just did
ROLLBACK

position                       names
---------------                ------------------
it                             julie
management                     mark, jane
sales                          bob, joe, donald

Bastard Programmer from Hell

ID       Name
-----------------
1        Party
2        Meeting

ID     Name
------------
1      Bob
2      Joe
3      Suzie

UID   EventID     EventType     Person
-------------------------------------------------------
1     1           1             1
2     1           1             2
3     1           1             3
4     2           1             1
5     2           1             3
6     3           2             1
7     3           2             2

EventID     EventTypeName      People
-------------------------------------------------------
1           Party              Bob, Joe, Suzie
2           Party              Bob, Suzie
3           Meeting            Bob, Joe

nallelcm wrote:

here is a better example

nallelcm wrote:

Want the output to look like

Bastard Programmer from Hell

BEGIN TRANSACTION
CREATE TABLE #EventType
(
 ID     INT
,[Name] VARCHAR(50)
)
INSERT INTO #EventType
      SELECT 1        ,'Party'
UNION SELECT 2        ,'Meeting'
UNION SELECT 3        ,'Something else that wasn''t mentioned'
 
CREATE TABLE #Person
(
 ID     INT
,[Name] VARCHAR(20)
)
INSERT INTO #Person
      SELECT 1      ,'Bob'
UNION SELECT 2      ,'Joe'
UNION SELECT 3      ,'Suzie'

CREATE TABLE [#Event]
(
 UID        INT
,EventID    INT
,EventType  INT
,Person     INT
)
INSERT INTO [#Event]
      SELECT 1,     1,           1,             1
UNION SELECT 2,     1,           1,             2
UNION SELECT 3,     1,           1,             3
UNION SELECT 4,     2,           1,             1
UNION SELECT 5,     2,           1,             3
UNION SELECT 6,     3,           2,             1
UNION SELECT 7,     3,           2,             2

SELECT DISTINCT EventId
           INTO #SomeTable
           FROM [#Event]

SELECT EventId
      ,[Name]
      ,SUBSTRING(CompoundColumn, 0, LEN(CompoundColumn) - LEN(', '))
      AS People
   FROM (
          SELECT ST.EventId
                ,ET.[Name]
                ,REPLACE(REPLACE(
                 (SELECT P.[Name]
                    FROM #Event E
                    JOIN #Person P ON E.Person = P.ID
                   WHERE E.EventID = ST.EventID
                    FOR XML AUTO), 
                 '<P Name="', ''), '"/>', ', '
                 ) AS CompoundColumn
            FROM #SomeTable ST
            LEFT JOIN #EventType ET ON ST.EventId = ET.ID
        ) AS tmp_cte

ROLLBACK

EventID     EventTypeName      People
-------------------------------------------------------
1           Party              Bob, Joe, Suzie
2           Party              Bob, Suzie
3           Meeting            Bob, Joe

EventId  Name                                       People
-------  ---------------------------------------    -----------------
1        Party                                      Bob, Joe, Suzie
2        Meeting                                    Bob, Suzie
3        Something else that wasn't mentioned       Bob, Joe

Bastard Programmer from Hell

DECLARE @String VARCHAR(8000)
set @String = ''
 
SELECT @String = @String + 'Name:' + ISNULL(Name,'NULL') + ' Age:' + Age + ' Total:' + Total + '; '
FROM TABLE

print @String

DECLARE @Temp TABLE(Name VARCHAR(20),Age TINYINT, Total INT)

INSERT INTO @Temp(Name,Age,Total)
SELECT 'Ram',26,800
UNION ALL
SELECT 'kumar',36,300
UNION ALL
SELECT 'Sam',34,200

DECLARE @SQLStr VARCHAR(8000)
SET @SQLStr = ''
 
SELECT @SQLStr = @SQLStr + 'Name:' + Name + ' Age:' + CAST(Age AS VARCHAR) + ' Total:' + CAST(Total AS VARCHAR) + '; '
FROM @Temp
 
PRINT @SQLStr