Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Changing the output of select.

Author  Topic 

cbillson
Starting Member

2 Posts
Posted - 2015-02-18 : 07:00:06
Hi, not sure how to describe this, what i'm trying to do may have a name, but finding very little via google.

I have a table: it contains a list of data belonging to a name, however the field 'name' defines the source.

--------------------------------
name |field1 |field1data |
--------------------------------
test1 |in |123
test1 |out |345
test2 |in |123
test2 |out |456
test3 |in |123
test3 |out |456


I'm trying to get the data out of the database in a columed format, I would be quite comfortable doing this if 'in' and 'out' where there own field.

The output i'm trying to get is:

--------------------------------
name |data |data |
--------------------------------
test1 |123 |456
test2 |123 |456
test3 |123 |456

in an ideal world i'd like to be able to have the header the name of the field data - eg

--------------------------------
name |in |out |
--------------------------------
test1 |123 |456
test2 |123 |456
test3 |123 |456

I can use - select name,field1data from table where field1 in ("in","out")

but this returns each piece of data as a new row - eg:

-------------------------
name |field1data |
-------------------------
test1 |123
test1 |456
test2 |123
test2 |456
test3 |123
test3 |456


I've probably included lots of info above to over describe something extremely simple :) but I have no idea where to start on this - so any help really appreciated.

Thanks

pradeepbliss
Starting Member

28 Posts
Posted - 2015-02-18 : 09:24:08
SET IDENTITY_INSERT [dbo].[TestPivot] ON
INSERT [dbo].[TestPivot] ([id], [name], [field1], [field1data]) VALUES (1, N'test1', N'INFIELD', 123)
INSERT [dbo].[TestPivot] ([id], [name], [field1], [field1data]) VALUES (2, N'test1', N'OUTFIELD', 345)
INSERT [dbo].[TestPivot] ([id], [name], [field1], [field1data]) VALUES (3, N'test2', N'INFIELD', 123)
INSERT [dbo].[TestPivot] ([id], [name], [field1], [field1data]) VALUES (4, N'test2', N'OUTFIELD', 456)
INSERT [dbo].[TestPivot] ([id], [name], [field1], [field1data]) VALUES (5, N'test3', N'INFIELD', 123)
INSERT [dbo].[TestPivot] ([id], [name], [field1], [field1data]) VALUES (6, N'test3', N'OUTFIELD', 456)
SET IDENTITY_INSERT [dbo].[TestPivot] OFF

Query:

select name,INFIELD,OUTFIELD from (select name,field1data,field1 from TestPivot) up pivot (sum(field1data)for field1 in(INFIELD,OUTFIELD)) as pvt

Result:

name INFIELD OUTFIELD
test1 123 345
test2 123 456
test3 123 456
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site