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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 get field names as data
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

lmayer4
Starting Member

USA
33 Posts

Posted - 01/02/2014 :  14:39:10  Show Profile  Reply with Quote
I am trying to get some data out of a table. It needs to be displayed like this:


VZ2Wire       DSLAMGLite
Krone/Port    Unit/Slot/Port
  1-2            1-6-2


but it is in the table like this:

type	        unit	krone	slot	port
VZ2Wire	        NULL	1	NULL	2
DSLAMGLite	1	NULL	6	2

This is the query to get the data above:

SELECT p.type, unit,krone,slot,port
FROM PTDbCircuits c
	inner join PTDbPairs p
		on c.trackid = p.FinLineId
	inner join PTDbTrackRef t
		on c.trackid = t.trackid


So it looks like I need to pull the field names if they are not null and make them act like data. I know how to get a field name from a table but not when it interacts with the data.


Any thoughts would be great.

Thanks!

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 01/03/2014 :  01:27:01  Show Profile  Reply with Quote
See illustration here

declare @t table
(
type	varchar(30),
unit int,
krone int,
slot int,
port int
)
insert @t
values('VZ2Wire',        NULL,	1,	NULL,	2),
('DSLAMGLite',	1,	NULL,	6,	2)

;With CTE
AS
(
select *
from @t
unpivot (val for cat in (unit,krone,slot,port))u
)
select [DSLAMGLite],[VZ2Wire]
from
(
select TYPE,
STUFF((SELECT '/' + cat from CTE where TYPE = c1.TYPE FOR XML PATH('')),1,1,'') AS cats,
1 AS ItemOrd
from (select distinct TYPE from CTE)c1
UNION ALL
select TYPE,
STUFF((SELECT '-' + CAST(val as varchar(10)) from CTE where TYPE = c1.TYPE FOR XML PATH('')),1,1,'') AS vals,
2
from (select distinct TYPE from CTE)c1
)m
PIVOT (MAX(cats) FOR TYPE IN ([DSLAMGLite],[VZ2Wire]))p


output
----------------------------------------
DSLAMGLite	VZ2Wire
----------------------------------------
unit/slot/port	krone/port
1-6-2	        1-2



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 01/03/2014 :  01:27:58  Show Profile  Reply with Quote
To make it dynamic see

http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

lmayer4
Starting Member

USA
33 Posts

Posted - 01/03/2014 :  11:55:52  Show Profile  Reply with Quote
Thanks again for the help!I will give this a shot.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 01/04/2014 :  05:09:28  Show Profile  Reply with Quote
cool
let me know how you got on!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000