November 26, 2014 at 7:48 am
Hello,
I am working to create a phone list that will contain Last Name, First Name, and Phone Number sorted by last name. For printing purposes I would like to have three columns of data instead of the standard of one column.
Is it possible to create a query to present data in three columns showing the data side by side?
November 26, 2014 at 7:52 am
it depends on the data.
if you are smart, and are storing them as three distinct strings in three columns, then yes of course. you simp-ly select three columns.
if you are following worst practices, and just have a single string with three or more data elements in them, then you have to parse that data out into three columns.
show us some more details on your data.
how is it stored now, and how are you getting your datA?
show us the actual query, ie
SELECT FirstName,
LastName,
PhoneNumber,
FROM Contacts
Lowell
November 26, 2014 at 8:07 am
Hi Lowell,
Thank you for your reply. The data each have their own columns.
My current query is: SELECT EID, LName, FName, Phone FROM EmployeeContacts ORDER BY LName
How would I turn this into 3 columns?
November 26, 2014 at 8:17 am
Meatloaf (11/26/2014)
Hi Lowell,Thank you for your reply. The data each have their own columns.
My current query is: SELECT EID, LName, FName, Phone FROM EmployeeContacts ORDER BY LName
How would I turn this into 3 columns?
ok, i'm confused.
they are already seperate columns(four, technically, with the EID)
I'm thinking this is an issue where somethign is being left out of the explanation, here.
Do you have some other step(an SSRS report?) that is concatenating them together? and you wnat to view them one way, but export them another?
rather than playing with it as far as visible vs printed/exported results,, i'd simply modify the report: whatever is concatenating it, you need to eliminate that step, i guess, so that the three columns remain distinct throught he whole process.
Lowell
November 26, 2014 at 8:19 am
I believe the OP is asking how to display 3 groups of columns like you might find in a phone book.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 26, 2014 at 8:26 am
Hi,
I apologize for the confusion. Yes, ultimately I will be using SSRS (I am a newbie with SSRS) and users will be printing out phone lists. I would like to have the data optimize the paper real estate as much as possible.
On one single page, I would like to display a total of 9 columns:
Last Name | First Name | Phone | Last Name | First Name | Phone| Last Name | First Name | Phone
Instead of:
Last Name | First Name | Phone
November 26, 2014 at 8:56 am
So this has nothing to do with the query. It's the settings within SSRS that will allow you to have multiple columns.
Unfortunately, I don't know SSRS well enough to suggest how to do that.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 27, 2014 at 4:21 am
Microsoft call this a newsletter-style report. It only works for me when I export the report to PDF. The report rendered in my browser still has only one column.
Here is a link to msdn which explains how to do it.
http://msdn.microsoft.com/en-us/library/ms159107%28v=sql.100%29.aspx
That URL also has another link at the bottom to a site which you might find useful.
November 27, 2014 at 5:25 am
OK, SSRS is a different board.
One way would be to add a modulus column to the results set so that each line gets a 0, 1 or 2 in the modulus column. You can then create three tables (within a list) - depends on which version of SSRS you are using. and filter each table for the modulus value.
Assuming that you would want to break the report by department (this break would be in the List), I would create the modulus based on the RANK() OVER(PARTITION BY DEPTCODE ORDER BY EMPID) otherwise you can't guarantee that the first record for a department is going to end up with a RANK [and therefore a modulus] of 1
If you want any more help, post back.
NOTE: This will make the records read left to right then top to bottom. If you want top to bottom then left to right, you would need to precalc the number of records in each group and define their column number based on which triptile (is that the 1/3 equivalent of a quartile?) they are in.
November 27, 2014 at 5:52 am
It took me a little while to figure this out, and it may still need some further tweaking, but this query returns the rows in a phone book format:
declare @data table (ID int, FirstName varchar(20), Surname varchar(20), phone varchar(10))
insert into @data values
(1,'Arthur','Adams',123),
(2,'Betsy','Bloom',456),
(3,'Charles','Crighton',147),
(4,'Derek','Domino',258),
(5,'Eric','Evans',369),
(6,'Frank','Figgs',789),
(7,'Graham','Green',321),
(8,'Harold','Howitzer',654),
(9,'Ian','Imbecile',987),
(10,'Jane','Jerky',741),
(11,'Kathy','Kreme',852),
(12,'Leonard','Lion',963),
(13,'Mike','Mump',159),
(14,'Nigel','Noggin',951),
(15,'Oliver','Owlman',357)
declare @cols int = 3
declare @entriesPerRow int
declare @Reccount int
select @Reccount = COUNT(*) from @data
set @entriesPerRow = case when @Reccount % @cols = 0 then @Reccount / @cols else (@Reccount / @cols)+1 end
select isnull([1],'') as Column1,isnull([2],'') as Column2,isnull([3],'') as Column3,isnull([4],'') as Column4,isnull([5],'') as Column5,isnull([6],'') as Column6
from
(select t.PhoneEntry
, ((rn-1)/@entriesPerRow)+1 as ColNumber
, case when t.rn%@entriesPerRow = 0 then @entriesPerRow else t.rn%@entriesPerRow end as RowNumber
from
( select surname +' ' +firstname +' '+phone as PhoneEntry, row_number() over (order by Surname, Firstname) as rn
from @data) as t)
as p
pivot
(max(PhoneEntry) for ColNumber in ([1], [2], [3], [4],[5],[6])) as pvt
You can add as many columns as you need into the pivot, and it fills however many you've specified in the @cols variable
November 27, 2014 at 8:04 am
I am sure this is easier, and it works for sub-groupings like dept (or team, or office, or region)
declare @data table (ID int, FirstName varchar(20), Surname varchar(20), phone varchar(10), Dept varchar(10))
insert into @data values
(1,'Arthur','Adams',123,'Finance'),
(2,'Betsy','Bloom',456,'HR'),
(3,'Charles','Crighton',147,'Finance'),
(4,'Derek','Domino',258,'Sales'),
(5,'Eric','Evans',369,'Sales'),
(6,'Frank','Figgs',789,'Finance'),
(7,'Graham','Green',321,'Sales'),
(8,'Harold','Howitzer',654,'HR'),
(9,'Ian','Imbecile',987,'HR'),
(10,'Jane','Jerky',741,'Finance'),
(11,'Kathy','Kreme',852,'Finance'),
(12,'Leonard','Lion',963,'HR'),
(13,'Mike','Mump',159,'Sales'),
(14,'Nigel','Noggin',951,'HR'),
(15,'Oliver','Owlman',357,'Finance')
;
WITH CTE AS
(
select *,RANK() OVER (PARTITION BY Dept ORDER BY Surname,firstname) as 'RANK' FROM @data
)
select *, Rank % 3 AS 'ColumnGroup' from CTE order by Dept,Surname,FirstName
December 1, 2014 at 7:41 am
Thank you everyone.
December 1, 2014 at 7:49 am
aaron.reese (11/27/2014)
I am sure this is easier, and it works for sub-groupings like dept (or team, or office, or region)
declare @data table (ID int, FirstName varchar(20), Surname varchar(20), phone varchar(10), Dept varchar(10))
insert into @data values
(1,'Arthur','Adams',123,'Finance'),
(2,'Betsy','Bloom',456,'HR'),
(3,'Charles','Crighton',147,'Finance'),
(4,'Derek','Domino',258,'Sales'),
(5,'Eric','Evans',369,'Sales'),
(6,'Frank','Figgs',789,'Finance'),
(7,'Graham','Green',321,'Sales'),
(8,'Harold','Howitzer',654,'HR'),
(9,'Ian','Imbecile',987,'HR'),
(10,'Jane','Jerky',741,'Finance'),
(11,'Kathy','Kreme',852,'Finance'),
(12,'Leonard','Lion',963,'HR'),
(13,'Mike','Mump',159,'Sales'),
(14,'Nigel','Noggin',951,'HR'),
(15,'Oliver','Owlman',357,'Finance')
;
WITH CTE AS
(
select *,RANK() OVER (PARTITION BY Dept ORDER BY Surname,firstname) as 'RANK' FROM @data
)
select *, Rank % 3 AS 'ColumnGroup' from CTE order by Dept,Surname,FirstName
Careful now... that offsets everything by 1 group. The first Group 0 is missing because Modulus works with a 0 base, not the 1 base of Rank. You need to subtract 1 from rank to make it work properly.
It also doesn't do an actual pivot of the data.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 2, 2014 at 5:09 am
See if this article addresses your problem:
http://blog.hoegaerden.be/2012/11/01/creating-multiple-column-reports/
- Brian
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply