July 28, 2010 at 9:42 am
example SELECT:
SELECT COMPANYNAME, COUNT(USER_id) AS NUM_USERS
FROM COMPANY_USER
GROUP BY COMPANYNAME
ORDER BY COUNT(USER_id) DESC
will return something like:
COMPANYNAME, NUM_USERS
company A, 3
company B, 2
How do I add a third column onto the results that would display a string with the user id's in that company? For example:
COMPANYNAME, NUM_USERS, USERS
company A, 3, [442, 443, 446]
company B, 2, [532, 456]
Thanks in advance for any assistance!!!
(fyi, using SQL Server 2005)
July 28, 2010 at 10:05 am
Just a thought...but what happens when
COMPANYNAME, NUM_USERS
company A, 300
company B, 2500
COMPANYNAME, NUM_USERS, USERS
company A, 3, [442, 443, 446................................................................]
company B, 2, [532, 456.....................
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 28, 2010 at 10:07 am
I'll jump that hurdle when it comes. The data that I'm looking at and the situation I need this for shouldn't be a problem.
July 28, 2010 at 10:29 am
Mike4King (7/28/2010)
I'll jump that hurdle when it comes. The data that I'm looking at and the situation I need this for shouldn't be a problem.
fair enough ....possibly the following article and associated discussion may assist you
http://www.sqlservercentral.com/Forums/Topic802508-1672-1.aspx
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 28, 2010 at 10:35 am
--Start by making some test data
DECLARE @TABLE AS TABLE(
[COMPANYNAME] VARCHAR(35),
[USER_id] VARCHAR(3))
INSERT INTO @TABLE([COMPANYNAME],[USER_id])
SELECT 'Company A', '101'
UNION ALL SELECT 'Company A', '102'
UNION ALL SELECT 'Company A', '103'
UNION ALL SELECT 'Company B', '101'
UNION ALL SELECT 'Company B', '102'
--Now to the query
;WITH cte ( [COMPANYNAME], [USER_id List], [USER_id], length )
AS (SELECT [COMPANYNAME],
CAST('' AS VARCHAR(8000)),
CAST('' AS VARCHAR(8000)),
0
FROM @TABLE
GROUP BY [COMPANYNAME]
UNION ALL
SELECT p.[COMPANYNAME],
CAST([USER_id List] + CASE
WHEN length = 0 THEN ''
ELSE ', '
END + p.[USER_id] AS VARCHAR(8000)),
CAST(p.[USER_id] AS VARCHAR(8000)),
length + 1
FROM cte c
INNER JOIN @TABLE p
ON c.[COMPANYNAME] = p.[COMPANYNAME]
WHERE p.[USER_id] > c.[USER_id])
SELECT [COMPANYNAME],
[USER_id List]
FROM (SELECT [COMPANYNAME],
[USER_id List],
Rank() OVER ( PARTITION BY [COMPANYNAME] ORDER BY length DESC )
FROM cte) d ( [COMPANYNAME], [USER_id List], rank )
WHERE rank = 1
July 29, 2010 at 5:04 am
Woah! No need for anything that complex, try this:
SELECT CompanyName, x =
(
SELECT
CASE WHEN ROW_NUMBER() OVER(ORDER BY USER_ID) > 1 THEN ',' ELSE '' END + USER_ID FROM @table t1
WHERE t2.CompanyName = t1.CompanyName
FOR XML PATH('')
)
FROM
@Table t2
GROUP BY CompanyName
Based on this article http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply