SQL Server Forums
Profile | Register | 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
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

lmayer4
Starting Member

USA
31 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
52317 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
52317 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
31 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
52317 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  
 New 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.14 seconds. Powered By: Snitz Forums 2000