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
 transpose dynamic rows to columns

Author  Topic 

amurark
Yak Posting Veteran

55 Posts
Posted - 2015-03-23 : 10:17:59
please suggest how to transpose rows to columns . there are 800 dynamic rows
















device_id UserName ipaddr macaddr LastConnecttime OperatingSystem RAM CPU TOTALSIZE FREESPACE percentage DriveName Name
C0YGCEOOLT7CWQX TELENOR\T875850 10.84.108.28 8086f21ee600 3/19/2015 9:30:08 AM Microsoft Windows 7 Professional Version 6.1.7601 [Build 7601] [Service Pack 1] 8,090 GenuineIntel[2500MHz] 12,742,291,456.00 906,027,008.00 7 D: Adobe Flash Player 15 ActiveX [15.0.0.239]
C0YGCEOOLT7CWQX TELENOR\T875850 10.84.108.28 8086f21ee600 3/19/2015 9:30:08 AM Microsoft Windows 7 Professional Version 6.1.7601 [Build 7601] [Service Pack 1] 8,090 GenuineIntel[2500MHz] 12,742,291,456.00 906,027,008.00 7 D: Adobe Flash Player 15 Plugin [15.0.0.239]
C0YGCEOOLT7CWQX TELENOR\T875850 10.84.108.28 8086f21ee600 3/19/2015 9:30:08 AM Microsoft Windows 7 Professional Version 6.1.7601 [Build 7601] [Service Pack 1] 8,090 GenuineIntel[2500MHz] 12,742,291,456.00 906,027,008.00 7 D: Adobe Reader XI (11.0.06) [11.0.06]
C0YGCEOOLT7CWQX TELENOR\T875850 10.84.108.28 8086f21ee600 3/19/2015 9:30:08 AM Microsoft Windows 7 Professional Version 6.1.7601 [Build 7601] [Service Pack 1] 8,090 GenuineIntel[2500MHz] 12,742,291,456.00 906,027,008.00 7 D: Alcor Micro Smart Card Reader Driver [1.7.35.0]
C0YGCEOOLT7CWQX TELENOR\T875850 10.84.108.28 8086f21ee600 3/19/2015 9:30:08 AM Microsoft Windows 7 Professional Version 6.1.7601 [Build 7601] [Service Pack 1] 8,090 GenuineIntel[2500MHz] 12,742,291,456.00 906,027,008.00 7 D: Apple Application Support [3.1]
C0YGCEOOLT7CWQX TELENOR\T875850 10.84.108.28 8086f21ee600 3/19/2015 9:30:08 AM Microsoft Windows 7 Professional Version 6.1.7601 [Build 7601] [Service Pack 1] 8,090 GenuineIntel[2500MHz] 12,742,291,456.00 906,027,008.00 7 D: Apple Mobile Device Support [8.0.5.6]
C0YGCEOOLT7CWQX TELENOR\T875850 10.84.108.28 8086f21ee600 3/19/2015 9:30:08 AM Microsoft Windows 7 Professional Version 6.1.7601 [Build 7601] [Service Pack 1] 8,090 GenuineIntel[2500MHz] 12,742,291,456.00 906,027,008.00 7 D: Apple Software Update [2.1.3.127]


Ankita

gbritton
Master Smack Fu Yak Hacker

2780 Posts
Posted - 2015-03-23 : 10:31:38
First, Why? What would you (or anyone) do with a query result that has 800 columns in it? It's not humanly readable

Second, what column do you want to transpose? (It can't be all columns, since you cannot have more than 4096 columns per select and you have 12 columns and 800 rows = 9600 columns)

https://msdn.microsoft.com/en-us/library/ms143432.aspx
Go to Top of Page

sz1
Aged Yak Warrior

555 Posts
Posted - 2015-03-23 : 10:51:22
I think he means 800 rows using these top columns:

device_id UserName ipaddr macaddr LastConnecttime OperatingSystem RAM CPU TOTALSIZE FREESPACE percentage DriveName Name

If so, you can use BCP command or bulk insert function to a table with correct columns.

We are the creators of our own reality!
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts
Posted - 2015-03-23 : 11:02:24
quote:
Originally posted by sz1

I think he means 800 rows using these top columns:

device_id UserName ipaddr macaddr LastConnecttime OperatingSystem RAM CPU TOTALSIZE FREESPACE percentage DriveName Name


Yes, I believe so, but that is 12 columns * 800 rows = 9600 columns transposed. More than twice the legal limit.
quote:

If so, you can use BCP command or bulk insert function to a table with correct columns.

We are the creators of our own reality!


The data is already in SQL. How would BCP help?
Go to Top of Page

sz1
Aged Yak Warrior

555 Posts
Posted - 2015-03-23 : 11:43:53
Agh already in SQL ok, well yeah strange request!

We are the creators of our own reality!
Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts
Posted - 2015-03-23 : 13:49:40
I think amurark has loaded a log file into a table with one column, and he now has 800 rows. The way I read the description, he wants each row split into 12 column (still with 800 rows).

@amurark: am I right?
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts
Posted - 2015-03-23 : 14:11:51
quote:
Originally posted by bitsmed

I think amurark has loaded a log file into a table with one column, and he now has 800 rows. The way I read the description, he wants each row split into 12 column (still with 800 rows).



I took the word "transpose" to mean what it usually does :-) but you could be correct.
Go to Top of Page

amurark
Yak Posting Veteran

55 Posts
Posted - 2015-03-23 : 23:35:43
hi All thnaks for the reply if we have only 100 rows then please tell how we can do transpose.



Ankita
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts
Posted - 2015-03-24 : 08:47:12
Using your sample data, this will work for the seven rows you provided:


declare @ table (
device_id varchar(50), UserName varchar(50), ipaddr varchar(50), macaddr varchar(50), LastConnecttime datetime, OperatingSystem varchar(100), RAM varchar(50), CPU varchar(50), TOTALSIZE varchar(50), FREESPACE varchar(50), percentage varchar(50), DriveName varchar(50), Name varchar(50))
;
insert into @ (device_id, UserName, ipaddr, macaddr, LastConnecttime, OperatingSystem, RAM, CPU, TOTALSIZE, FREESPACE, percentage, DriveName, Name) values
--device_id UserName ipaddr macaddr LastConnecttime OperatingSystem RAM CPU TOTALSIZE FREESPACE percentage DriveName Name
('C0YGCEOOLT7CWQX', 'TELENOR\T875850', '10.84.108.28', '8086f21ee600', '3/19/2015 9:30:08 AM', 'Microsoft Windows 7 Professional Version 6.1.7601 [Build 7601] [Service Pack 1]', '8,090', 'GenuineIntel[2500MHz]', '12,742,291,456.00', '906,027,008.00', '7', 'D:', 'Adobe Flash Player 15 ActiveX [15.0.0.239]	   '),
('C0YGCEOOLT7CWQX', 'TELENOR\T875850', '10.84.108.28', '8086f21ee600', '3/19/2015 9:30:08 AM', 'Microsoft Windows 7 Professional Version 6.1.7601 [Build 7601] [Service Pack 1]', '8,090', 'GenuineIntel[2500MHz]', '12,742,291,456.00', '906,027,008.00', '7', 'D:', 'Adobe Flash Player 15 Plugin [15.0.0.239]	   '),
('C0YGCEOOLT7CWQX', 'TELENOR\T875850', '10.84.108.28', '8086f21ee600', '3/19/2015 9:30:08 AM', 'Microsoft Windows 7 Professional Version 6.1.7601 [Build 7601] [Service Pack 1]', '8,090', 'GenuineIntel[2500MHz]', '12,742,291,456.00', '906,027,008.00', '7', 'D:', 'Adobe Reader XI (11.0.06) [11.0.06]			   '),
('C0YGCEOOLT7CWQX', 'TELENOR\T875850', '10.84.108.28', '8086f21ee600', '3/19/2015 9:30:08 AM', 'Microsoft Windows 7 Professional Version 6.1.7601 [Build 7601] [Service Pack 1]', '8,090', 'GenuineIntel[2500MHz]', '12,742,291,456.00', '906,027,008.00', '7', 'D:', 'Alcor Micro Smart Card Reader Driver [1.7.35.0] '),
('C0YGCEOOLT7CWQX', 'TELENOR\T875850', '10.84.108.28', '8086f21ee600', '3/19/2015 9:30:08 AM', 'Microsoft Windows 7 Professional Version 6.1.7601 [Build 7601] [Service Pack 1]', '8,090', 'GenuineIntel[2500MHz]', '12,742,291,456.00', '906,027,008.00', '7', 'D:', 'Apple Application Support [3.1]				   '),
('C0YGCEOOLT7CWQX', 'TELENOR\T875850', '10.84.108.28', '8086f21ee600', '3/19/2015 9:30:08 AM', 'Microsoft Windows 7 Professional Version 6.1.7601 [Build 7601] [Service Pack 1]', '8,090', 'GenuineIntel[2500MHz]', '12,742,291,456.00', '906,027,008.00', '7', 'D:', 'Apple Mobile Device Support [8.0.5.6]		   '),
('C0YGCEOOLT7CWQX', 'TELENOR\T875850', '10.84.108.28', '8086f21ee600', '3/19/2015 9:30:08 AM', 'Microsoft Windows 7 Professional Version 6.1.7601 [Build 7601] [Service Pack 1]', '8,090', 'GenuineIntel[2500MHz]', '12,742,291,456.00', '906,027,008.00', '7', 'D:', 'Apple Software Update [2.1.3.127]			   ')
;

with input as
(
select rn = row_number() over(order by device_id), *
from @
)

select a.*, b.*, c.*, d.*, e.*, f.*, g.*
from input a
cross join input b 
cross join input c 
cross join input d 
cross join input e 
cross join input f 
cross join input g
where a.rn = 1
  and b.rn = 2
  and c.rn = 3
  and d.rn = 4
  and e.rn = 5
  and f.rn = 6
  and g.rn = 7


However, I'm still not convinced that this is what you really want. With this data, you have 91 columns. With 100 rows input, you would have 1300 columns. That's not really practical for human consumption.
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts
Posted - 2015-03-25 : 09:10:20
Take 2, doing an Excel-style transpose:


declare @ table (
device_id varchar(100), UserName varchar(100), ipaddr varchar(100), macaddr varchar(100), LastConnecttime varchar(100), OperatingSystem varchar(100), RAM varchar(100), CPU varchar(100), TOTALSIZE varchar(100), FREESPACE varchar(100), percentage varchar(100), DriveName varchar(100), Name varchar(100))
;
insert into @ (device_id, UserName, ipaddr, macaddr, LastConnecttime, OperatingSystem, RAM, CPU, TOTALSIZE, FREESPACE, percentage, DriveName, Name) values
--device_id          UserName           ipaddr          macaddr         LastConnecttime         OperatingSystem                                                                    RAM      CPU                      TOTALSIZE            FREESPACE         percentage DriveName Name
('C0YGCEOOLT7CWQX', 'TELENOR\T875850', '10.84.108.28', '8086f21ee600', '3/19/2015 9:30:08 AM', 'Microsoft Windows 7 Professional Version 6.1.7601 [Build 7601] [Service Pack 1]', '8,090', 'GenuineIntel[2500MHz]', '12,742,291,456.00', '906,027,008.00', '7',       'D:',     'Adobe Flash Player 15 ActiveX [15.0.0.239]	   '),
('C0YGCEOOLT7CWQX', 'TELENOR\T875850', '10.84.108.28', '8086f21ee600', '3/19/2015 9:30:08 AM', 'Microsoft Windows 7 Professional Version 6.1.7601 [Build 7601] [Service Pack 1]', '8,090', 'GenuineIntel[2500MHz]', '12,742,291,456.00', '906,027,008.00', '7',       'D:',     'Adobe Flash Player 15 Plugin [15.0.0.239]	   '),
('C0YGCEOOLT7CWQX', 'TELENOR\T875850', '10.84.108.28', '8086f21ee600', '3/19/2015 9:30:08 AM', 'Microsoft Windows 7 Professional Version 6.1.7601 [Build 7601] [Service Pack 1]', '8,090', 'GenuineIntel[2500MHz]', '12,742,291,456.00', '906,027,008.00', '7',       'D:',     'Adobe Reader XI (11.0.06) [11.0.06]			   '),
('C0YGCEOOLT7CWQX', 'TELENOR\T875850', '10.84.108.28', '8086f21ee600', '3/19/2015 9:30:08 AM', 'Microsoft Windows 7 Professional Version 6.1.7601 [Build 7601] [Service Pack 1]', '8,090', 'GenuineIntel[2500MHz]', '12,742,291,456.00', '906,027,008.00', '7',       'D:',     'Alcor Micro Smart Card Reader Driver [1.7.35.0] '),
('C0YGCEOOLT7CWQX', 'TELENOR\T875850', '10.84.108.28', '8086f21ee600', '3/19/2015 9:30:08 AM', 'Microsoft Windows 7 Professional Version 6.1.7601 [Build 7601] [Service Pack 1]', '8,090', 'GenuineIntel[2500MHz]', '12,742,291,456.00', '906,027,008.00', '7',       'D:',     'Apple Application Support [3.1]				   '),
('C0YGCEOOLT7CWQX', 'TELENOR\T875850', '10.84.108.28', '8086f21ee600', '3/19/2015 9:30:08 AM', 'Microsoft Windows 7 Professional Version 6.1.7601 [Build 7601] [Service Pack 1]', '8,090', 'GenuineIntel[2500MHz]', '12,742,291,456.00', '906,027,008.00', '7',       'D:',     'Apple Mobile Device Support [8.0.5.6]		   '),
('C0YGCEOOLT7CWQX', 'TELENOR\T875850', '10.84.108.28', '8086f21ee600', '3/19/2015 9:30:08 AM', 'Microsoft Windows 7 Professional Version 6.1.7601 [Build 7601] [Service Pack 1]', '8,090', 'GenuineIntel[2500MHz]', '12,742,291,456.00', '906,027,008.00', '7',       'D:',     'Apple Software Update [2.1.3.127]			   ')
;

select colname, [1],[2],[3],[4],[5],[6],[7]
	from (
		select rn = ROW_NUMBER() over(order by device_id, UserName)
		     , *
		from @
	) src
unpivot
(
	colvalue for colname in ([device_id], UserName, ipaddr, macaddr, LastConnecttime, OperatingSystem, RAM, CPU, TOTALSIZE, FREESPACE, percentage, DriveName, Name)
) u
pivot
(
	max(colvalue) for rn in ([1],[2],[3],[4],[5],[6],[7])
) p
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