locked
Display multi rows from one record RRS feed

  • Question

  • User974979776 posted

    Hi,
    I want to display one record from database into multi rows and multi columns.
    how can I do that?

    thanks.

    Monday, November 7, 2016 6:45 PM

Answers

  • User-1078128378 posted

    Hi mubarak,

    I created a Script for your Issue .

    Run below script.

    --table creation
    CREATE TABLE Books( Id INT, Title VARCHAR(30) ) go
    --insert values in table Insert Into Books (Id,Title) Values (1,'Book-1'), (2,'Book-2'), (3,'Book-3'), (4,'Book-4') go
    -- display rows as columns script
    DECLARE @SQL varchar(MAX), @ColumnList varchar(MAX) SELECT @ColumnList= COALESCE(@ColumnList + ',','') + QUOTENAME(Title) FROM ( SELECT DISTINCT Title FROM Books ) T SET @SQL = ' WITH PivotData AS ( SELECT Title FROM Books ) SELECT ' + @ColumnList + ' FROM PivotData PIVOT ( MAX(Title) FOR Title IN (' + @ColumnList + ') ) AS PivotResult' EXEC (@SQL)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 8, 2016 10:48 AM

All replies

  • User-691209617 posted

    You can achieve this is to combine For XML Path and STUFF as follows

    SELECT 
       SS.SEC_NAME,
       STUFF((SELECT '; ' + US.USR_NAME 
              FROM USRS US
              WHERE US.SEC_ID = SS.SEC_ID
              ORDER BY USR_NAME
              FOR XML PATH('')), 1, 1, '') [SECTORS/USERS]
    FROM SALES_SECTORS SS
    GROUP BY SS.SEC_ID, SS.SEC_NAME
    ORDER BY 1

    Also check sql fiddle for example.

    http://sqlfiddle.com/#!3/c43e2/2

    Monday, November 7, 2016 6:54 PM
  • User-967720686 posted

    Hi Mubarak, 

    Below are the ways you could use. 

    Declare @Table Table (
    	LastName   Varchar(10),
    	FirstName  Varchar(50)
    )
    
    
    Insert	Into @Table (LastName, FirstName)
    Values	('Jones', 'David'),
    		('McBride', 'Shane'),
    		('McDonald', 'Jackie'),
    		('Campbell', 'Joseph')
    		
    		
    -- 1: 
    Declare @Str Varchar(Max)		
    
    Select	@Str = IsNull(@Str, '') + ', ' + FirstName + ' ' + LastName  
    From	@Table 
    
    Select STUFF(@Str, 1, 1, '')
    
    -- 2: 
    Select	STUFF((
    Select	', ' + FirstName + ' ' + LastName 
    From	@Table 
    		For Xml Path('')), 1, 1, '')

    Monday, November 7, 2016 11:35 PM
  • User-2057865890 posted

    Hi Mubarak200,

    You could use STUFF or FOR XML.

    CREATE TABLE SALES_SECTORS(
    SEC_ID INT,
    SEC_NAME VARCHAR(30))
    GO
    CREATE TABLE USRS(
    USR_ID INT,
    USR_NAME VARCHAR(30),
    SEC_ID INT
    )
    GO
    CREATE TABLE ADV_CAMPAIGN(
    ADV_ID INT,
    ADV_NAME VARCHAR(30)
    )
    GO
    CREATE TABLE USR_ADV_CAMPAIGN(
    USR_ID INT,
    ADV_ID INT
    )
    GO
    CREATE TABLE SEC_ADV_CAMPAIGN(
    SEC_ID INT,
    ADV_ID INT
    )
    GO
    INSERT INTO SALES_SECTORS( SEC_ID, SEC_NAME ) VALUES ( 1, 'ENTERTAINMENT' )
    INSERT INTO SALES_SECTORS( SEC_ID, SEC_NAME ) VALUES ( 2, 'CLOTHES' )
    GO
    INSERT INTO USRS( USR_ID, USR_NAME, SEC_ID ) VALUES ( 1, 'ANDERSON', 1 )
    INSERT INTO USRS( USR_ID, USR_NAME, SEC_ID ) VALUES ( 2, 'CHARLES', 1 )
    INSERT INTO USRS( USR_ID, USR_NAME, SEC_ID ) VALUES ( 3, 'DANNY', 1 )
    INSERT INTO USRS( USR_ID, USR_NAME, SEC_ID ) VALUES ( 4, 'LUCAS', 1 )
    INSERT INTO USRS( USR_ID, USR_NAME, SEC_ID ) VALUES ( 5, 'KEITH', 2 )
    INSERT INTO USRS( USR_ID, USR_NAME, SEC_ID ) VALUES ( 6, 'STEFAN', 2 )
    INSERT INTO USRS( USR_ID, USR_NAME, SEC_ID ) VALUES ( 7, 'EDUARD', 2 )
    INSERT INTO USRS( USR_ID, USR_NAME, SEC_ID ) VALUES ( 8, 'BRAD', 2 )
    GO
    INSERT INTO ADV_CAMPAIGN( ADV_ID, ADV_NAME ) VALUES ( 1, 'SONY ENTERTAINMENT' )
    INSERT INTO ADV_CAMPAIGN( ADV_ID, ADV_NAME ) VALUES ( 2, 'BEATS SOUNDS' )
    INSERT INTO ADV_CAMPAIGN( ADV_ID, ADV_NAME ) VALUES ( 3, 'BOOSE' )
    INSERT INTO ADV_CAMPAIGN( ADV_ID, ADV_NAME ) VALUES ( 4, 'POLO RALPH LAUREN' )
    INSERT INTO ADV_CAMPAIGN( ADV_ID, ADV_NAME ) VALUES ( 5, 'LACOSTE' )
    GO
    INSERT INTO USR_ADV_CAMPAIGN( USR_ID, ADV_ID ) VALUES ( 1, 1 )
    INSERT INTO USR_ADV_CAMPAIGN( USR_ID, ADV_ID ) VALUES ( 1, 2 )
    INSERT INTO USR_ADV_CAMPAIGN( USR_ID, ADV_ID ) VALUES ( 2, 2 )
    INSERT INTO USR_ADV_CAMPAIGN( USR_ID, ADV_ID ) VALUES ( 2, 3 )
    INSERT INTO USR_ADV_CAMPAIGN( USR_ID, ADV_ID ) VALUES ( 3, 3 )
    INSERT INTO USR_ADV_CAMPAIGN( USR_ID, ADV_ID ) VALUES ( 4, 2 )
    INSERT INTO USR_ADV_CAMPAIGN( USR_ID, ADV_ID ) VALUES ( 5, 4 )
    INSERT INTO USR_ADV_CAMPAIGN( USR_ID, ADV_ID ) VALUES ( 6, 5 )
    INSERT INTO USR_ADV_CAMPAIGN( USR_ID, ADV_ID ) VALUES ( 7, 4 )
    INSERT INTO USR_ADV_CAMPAIGN( USR_ID, ADV_ID ) VALUES ( 8, 5 )
    GO
    INSERT INTO SEC_ADV_CAMPAIGN( SEC_ID, ADV_ID ) VALUES ( 1, 1 )
    INSERT INTO SEC_ADV_CAMPAIGN( SEC_ID, ADV_ID ) VALUES ( 1, 2 )
    INSERT INTO SEC_ADV_CAMPAIGN( SEC_ID, ADV_ID ) VALUES ( 1, 3 )
    INSERT INTO SEC_ADV_CAMPAIGN( SEC_ID, ADV_ID ) VALUES ( 2, 4 )
    INSERT INTO SEC_ADV_CAMPAIGN( SEC_ID, ADV_ID ) VALUES ( 2, 5 )
    GO
    
    
    SELECT 
       SS.SEC_NAME, 
       US.USR_NAME 
    FROM SALES_SECTORS SS 
    INNER JOIN USRS US ON US.SEC_ID = SS.SEC_ID 
    ORDER BY 1, 2 
    FOR XML PATH('')

    reference: Rolling up multiple rows into a single row and column for SQL Server data

    Best Regards,

    Chris

    Tuesday, November 8, 2016 8:41 AM
  • User974979776 posted

    Thank you all

     

    I think I didn't write the question correctly

    I have a table with the following fields and name it "Book". Like this

    Data Type

    Column Name

    Int( Primary Key. So set Is Identity=True)

    Id

    varchar(100)

    Title

     

    I want to display "Title" record in DataGrid or table or anything but must be like this picture:

    Book 6

    Book 5

    Book 4

    Book 3

    Book 2

    Book 1

    Book  12

    Book 11

    Book 10

    Book  9

    Book 8

    Book 7

    Book  18

    Book 17

    Book 16

    Book 15

    Book 14

    Book 13

    Book 24

    Book 23

    Book 22

    Book 21

    Book 20

    Book 19

     

     

    Tuesday, November 8, 2016 9:39 AM
  • User-1078128378 posted

    Hi mubarak,

    I created a Script for your Issue .

    Run below script.

    --table creation
    CREATE TABLE Books( Id INT, Title VARCHAR(30) ) go
    --insert values in table Insert Into Books (Id,Title) Values (1,'Book-1'), (2,'Book-2'), (3,'Book-3'), (4,'Book-4') go
    -- display rows as columns script
    DECLARE @SQL varchar(MAX), @ColumnList varchar(MAX) SELECT @ColumnList= COALESCE(@ColumnList + ',','') + QUOTENAME(Title) FROM ( SELECT DISTINCT Title FROM Books ) T SET @SQL = ' WITH PivotData AS ( SELECT Title FROM Books ) SELECT ' + @ColumnList + ' FROM PivotData PIVOT ( MAX(Title) FOR Title IN (' + @ColumnList + ') ) AS PivotResult' EXEC (@SQL)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 8, 2016 10:48 AM
  • User974979776 posted

    thanks murali

    thank you for all

    Tuesday, November 8, 2016 11:30 AM