Creating a Query to show data side by side

  • 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?

  • 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,

    Email

    FROM Contacts

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 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?

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • I believe the OP is asking how to display 3 groups of columns like you might find in a phone book.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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

  • 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

  • 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.

  • 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.

  • 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

  • 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

  • Thank you everyone.

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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