t-sql query help

CREATE TABLE #EMPS (Cnt INT, EmpID INT, EmpName VARCHAR(30));
GO
INSERT INTO #EMPS (Cnt, EmpID,EmpName) VALUES (1,417,'Mary Smith');
INSERT INTO #EMPS ([Cnt],[EmpID],[EmpName]) VALUES (4,421,'John Smith');
INSERT INTO #EMPS ([Cnt],[EmpID],[EmpName]) VALUES (28,448,'Sam Ahto');
INSERT INTO #EMPS ([Cnt],[EmpID],[EmpName]) VALUES (12,461,'Alaina Ahto');
INSERT INTO #EMPS ([Cnt],[EmpID],[EmpName]) VALUES (9,447,'Edward Lyer');

select * from #EMPS

 
SELECT      Mgt_ID = 445,
            convert(varchar(10), GETDATE(), 120) create_date,
            'Work overdue 13 days or more' as overDue_Desc--,
/*            Detail_Desc =
            (

                  SELECT       
                        CNT,
                        EmpID,
                        EmpName
                  FROM      #EMPS  
                  
            )
            */
 

DROP TABLE #EMPS;


The result:

Mgt_ID       Create_Date      OverDue_Desc                  Detail_Desc
445      2015-05-21      Work overdue 13 days or more      Mary Smith, EmpID=417, Count=1, John Smith, EmpID=421, Count=4, Sam Ahto, EmpID=448, Count=28, Alaina Ahto, EmpID=461, Count=12,Edward Lyer, EmpID=447, Count=9


Thank you very much!!
jfreeman2010Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Okay, I'll bite.  What's your question?
0
Leo TorresSQL DeveloperCommented:
Thats the best question of the post.. Whats the question here?
0
jfreeman2010Author Commented:
I am try to get the result field: Detail_Desc from a select query:

    Detail_Desc =
            (

                  SELECT      
                        CNT,
                        EmpID,
                        EmpName
                  FROM      #EMPS  
                 
            )
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Leo TorresSQL DeveloperCommented:
Not sure you can set something equal to a query with multiple columns. Even with 1 column variable will only accept first value returned by query.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
No can do man.  A subquery for a SELECT column can only contain one column, and you have three.
So ... explain for us what exactly you are trying to set into Some_column

    Some_column=
             (

                   SELECT       
                         column_one,
                         column_two,
                         column_three
                   FROM      #EMPS  
                   
             ) 

Open in new window

0
jfreeman2010Author Commented:
I am try to make the result of:

 SELECT      
                         column_one,
                         column_two,
                         column_three
                   FROM      #EMPS  

To a text field.  

Exp: I need column_one (Name) + Column_two (column header name + value) + column_three (column header name + value).

The test query above will look like:
 Mary Smith, EmpID=417, Count=1, John Smith, EmpID=421, Count=4, Sam Ahto, EmpID=448, Count=28, Alaina Ahto, EmpID=461, Count=12,Edward Lyer, EmpID=447, Count=9
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Define 'result'.  

Addition, such as 1+2+3=6
Concatentation, such as '1'+'2'+'3'='123'
... or something else?

>Exp: I need column_one (Name) + Column_two (column header name + value) + column_three (column header name + value).
That would imply concatenation, so try this
SELECT  column_one + column_two + column_three FROM #EMPS

Open in new window

 
you may need to play with it a little to make it look pretty
SELECT  column_one + ' ' + bcolumn_two + ' ' + column_three FROM #EMPS  

Open in new window

Also if those columns are not text, say a numeric data type, then you'll have to convert to text in order to pull this off
SELECT  CAST(column_one as varchar(100)) + CAST(column_two as varchar(100)) + CAST(column_three as varchar(100)) FROM #EMPS

Open in new window

0
jfreeman2010Author Commented:
SELECT  column_one + bcolumn_two + column_three FROM #EMPS

result of the query:
Cnt      EmpID      EmpName
1      417      Mary Smith
4      421      John Smith
28      448      Sam Ahto
12      461      Alaina Ahto
9      447      Edward Lyer

I need to convert those 5 rec to 1 field as:
Mary Smith, EmpID=417, Count=1, John Smith, EmpID=421, Count=4, Sam Ahto, EmpID=448, Count=28, Alaina Ahto, EmpID=461, Count=12,Edward Lyer, EmpID=447, Count=9
0
jfreeman2010Author Commented:
One way I can do is fetch each rec and concatenate all the fields to a temp field until end of the query and assign to detail_desc field....

I am look better way to doing this...
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Something like this?
CREATE TABLE #EMPS (Cnt INT, EmpID INT, EmpName VARCHAR(30));
 GO
 INSERT INTO #EMPS (Cnt, EmpID,EmpName) VALUES (1,417,'Mary Smith');
 INSERT INTO #EMPS ([Cnt],[EmpID],[EmpName]) VALUES (4,421,'John Smith');
 INSERT INTO #EMPS ([Cnt],[EmpID],[EmpName]) VALUES (28,448,'Sam Ahto');
 INSERT INTO #EMPS ([Cnt],[EmpID],[EmpName]) VALUES (12,461,'Alaina Ahto');
 INSERT INTO #EMPS ([Cnt],[EmpID],[EmpName]) VALUES (9,447,'Edward Lyer');

 ;WITH CTE_Descr (Descr)
 AS
	(SELECT EmpName + ',EmpID=' + CAST(EmpID AS VARCHAR) + ',Count=' + CAST(Cnt AS VARCHAR)
	FROM #EMPS)
 SELECT Mgt_ID = 445,
		convert(varchar(10), GETDATE(), 120) create_date,
        'Work overdue 13 days or more' as overDue_Desc,
        STUFF((SELECT ',' + Descr
				FROM CTE_Descr 
				FOR XML PATH('')),1,1,'') AS Description

 DROP TABLE #EMPS;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jfreeman2010Author Commented:
yes,  Thank you very much!!
0
jfreeman2010Author Commented:
Thank you
0
jfreeman2010Author Commented:
How to exclude null ->  ;WITH CTE_Descr (Descr)
0
jfreeman2010Author Commented:
When:

SELECT EmpName + ',EmpID=' + CAST(EmpID AS VARCHAR) + ',Count=' + CAST(Cnt AS VARCHAR)
      FROM #EMPS

Return 0 rec.

This should return 0 rec:
SELECT Mgt_ID = 445,
            convert(varchar(10), GETDATE(), 120) create_date,
        'Work overdue 13 days or more' as overDue_Desc,
        STUFF((SELECT ',' + Descr
                        FROM CTE_Descr
                        FOR XML PATH('')),1,1,'') AS Description

Thank you,
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
You can't exclude that.
What you can do is to very first the existence of records:
CREATE TABLE #EMPS (Cnt INT, EmpID INT, EmpName VARCHAR(30));
 GO

IF EXISTS (SELECT TOP 1 * FROM #EMPS) 
	 WITH CTE_Descr (Descr)
	 AS
		(SELECT EmpName + ',EmpID=' + CAST(EmpID AS VARCHAR) + ',Count=' + CAST(Cnt AS VARCHAR)
		FROM #EMPS)
	 SELECT Mgt_ID = 445,
			convert(varchar(10), GETDATE(), 120) create_date,
			'Work overdue 13 days or more' as overDue_Desc,
			STUFF((SELECT ',' + Descr
					FROM CTE_Descr 
					FOR XML PATH('')),1,1,'') AS Description

 DROP TABLE #EMPS;

Open in new window

0
jfreeman2010Author Commented:
Great!!  Thank you!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.