May 7, 2012 at 10:08 pm
How do you something like this? :hehe:
CREATE TABLE [dbo].[Tbl](
[field1] [smallint] NULL,
[field2] [varchar](50) NULL
) ON [PRIMARY]
INSERT INTO [Test].[dbo].[Tbl] ([field1],[field2])VALUES(1,'Ann')
INSERT INTO [Test].[dbo].[Tbl] ([field1],[field2])VALUES(1,'Andy')
INSERT INTO [Test].[dbo].[Tbl] ([field1],[field2])VALUES(1,'Jen')
INSERT INTO [Test].[dbo].[Tbl] ([field1],[field2])VALUES(2,'Evan')
INSERT INTO [Test].[dbo].[Tbl] ([field1],[field2])VALUES(2,'Paul')
INSERT INTO [Test].[dbo].[Tbl] ([field1],[field2])VALUES(2,'Amy')
INSERT INTO [Test].[dbo].[Tbl] ([field1],[field2])VALUES(3,'Stan')
INSERT INTO [Test].[dbo].[Tbl] ([field1],[field2])VALUES(3,'John')
INSERT INTO [Test].[dbo].[Tbl] ([field1],[field2])VALUES(3,'Lee')
SELECT
'type1' = CASE WHEN field1 = 1 THEN field2 END,
'type2' = CASE WHEN field1 = 2 THEN field2 END,
'type3' = CASE WHEN field1 = 3 THEN field2 END
FROM Tbl
I want it to return 3 columns (type1,type2,type3).
type1 will have Ann, Andy and Jen.
type2 will have Evan, Paul and Amy.
type3 will have Stan, John and Lee.
May 8, 2012 at 1:15 am
This is my shot at it:
DECLARE @temp1 VARCHAR(MAX);
DECLARE @temp2 VARCHAR(MAX);
DECLARE @temp3 VARCHAR(MAX);
SELECT @temp1 = COALESCE(@temp1,'') + ',' + CHAR(39) + field2 + CHAR(39) + ''
FROM tbl Where field1 = 1
SET @temp1 = STUFF(@temp1,1,1,'')
SELECT @temp2 = COALESCE(@temp2,'') + ',' + CHAR(39) + field2 + CHAR(39) + ''
FROM tbl Where field1 = 2
SET @temp2 = STUFF(@temp2,1,1,'')
SELECT @temp3 = COALESCE(@temp3,'') + ',' + CHAR(39) + field2 + CHAR(39) + ''
FROM tbl Where field1 = 3
SET @temp3 = STUFF(@temp3,1,1,'')
Select Distinct
(Case When field1 = 1 then 'Type '+Cast(field1 AS Varchar(1))
When field1 = 2 then 'Type '+Cast(field1 AS Varchar(1))
When field1 = 3 then 'Type '+Cast(field1 AS Varchar(1))
Else ''
End
) As Type,
(Case When field1 = 1 then @temp1
When field1 = 2 then @temp2
When field1 = 3 then @temp3
Else ''
End
) As Names
From Tbl
Hope this helps.
The pros here might come up with something better.
I'll re-post if I can get this code a little shorter
May 8, 2012 at 2:47 am
try this
select DISTINCT Field1,
STUFF
(
(
SELECT ',' + field2
FROM [Tbl] t2
WHERE t1.field1 = t2.field1
ORDER BY field1
FOR XML PATH('')
), 1, 1, ''
) AS 'Field2'
from [Tbl] as t1
May 8, 2012 at 3:22 am
Sony Francis (5/8/2012)
try this
select DISTINCT Field1,
STUFF
(
(
SELECT ',' + field2
FROM [Tbl] t2
WHERE t1.field1 = t2.field1
ORDER BY field1
FOR XML PATH('')
), 1, 1, ''
) AS 'Field2'
from [Tbl] as t1
Yes. This works.
Worked on the above query and got to this one....was just about to post.:-D
May 8, 2012 at 6:50 am
When I run the query in sql server management studio I need it to return 3 columns (type1, type2, type3) with 3 records each.
column type1 has the following 3 records:
Ann
Andy
Jen
column type2 has the following 3 records:
Evan
Paul
Amy
column type3 has the following 3 records:
Stan
John
Lee
May 8, 2012 at 7:37 am
michaelyarbrough1975 (5/8/2012)
When I run the query in sql server management studio I need it to return 3 columns (type1, type2, type3) with 3 records each.column type1 has the following 3 records:
Ann
Andy
Jen
column type2 has the following 3 records:
Evan
Paul
Amy
column type3 has the following 3 records:
Stan
John
Lee
What about if number of records qualifying for Type1, 2 and 3 is not the same?
SQLServer is not Excel spreadsheet, so you cannot have 3 "records" in one columns and 5 "records" in another column.
May 8, 2012 at 7:53 am
The best what you can do is:
;with r1 as (select ROW_NUMBER() over (order by field2) rn, field2
from Tbl where field1 = 1)
,r2 as (select ROW_NUMBER() over (order by field2) rn, field2
from Tbl where field1 = 2)
,r3 as (select ROW_NUMBER() over (order by field2) rn, field2
from Tbl where field1 = 3)
select r1.field2 Type1, r2.field2 Type2, r3.field2 Type3
from r1
full join r2 on r2.rn = r1.rn
full join r3 on r3.rn = isnull(r1.rn,r2.rn)
But, your requirements doesn't look right, as it hard to see any point in what you are trying to do...
May 8, 2012 at 10:50 am
This will produce what you are looking for, but I agree with Eugene that the reasons for producing this result are suspect.
SELECT STUFF(( SELECT ', ' + field2 FROM #Tbl WHERE field1 = 1 ORDER BY field2 FOR XML PATH('') ), 1, 2, '') AS Type1
, STUFF(( SELECT ', ' + field2 FROM #Tbl WHERE field1 = 2 ORDER BY field2 FOR XML PATH('') ), 1, 2, '') AS Type2
, STUFF(( SELECT ', ' + field2 FROM #Tbl WHERE field1 = 3 ORDER BY field2 FOR XML PATH('') ), 1, 2, '') AS Type3
NOTE: Since order isn't guaranteed unless you specify an ORDER BY clause, I have sorted them in alphabetical order.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 8, 2012 at 5:31 pm
Thank you. This works.
May 8, 2012 at 10:29 pm
May 8, 2012 at 10:56 pm
SELECE FIELD1,FIELD2
FROM TABLENAME
GROUP BY FIELD1,FIELD2;
THIS MIGHT WORK
May 8, 2012 at 11:37 pm
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply