none
cross kind of table RRS feed

  • Question

  • Hi,

    I have a bit of a challange.

    I have a table with spare parts (number, description, location, numberAtlocation)
    The field location has a integer which represents a record in another table.

    I need to get a table that looks like this:

    col1, col2, col3, col4, col5,...,coln

    I want the column col5 to n to have the name of each location and as a value for each spare part, the number of items present at that location.

    so table 1:

    number  description   location   numberatlocation
    12345    test              1000       5
    12345    test              1001       2
    12345    test              1002       4
    12345    test              1003       9
    12345    test              1004       7

    Table 2:

    code   name
    1000   location1
    1001   location2
    1002   location3
    1003   location4
    1004   location5

    And the table I would like to get:

    Number   description   location1   location2   location3   location4   location5
    12345     test              5              2              4              9              7

    Can someone please show me an example of how to do this? (if at all possible?)

    rg,
    Eric

     

     

     

    Monday, December 6, 2010 11:49 AM

Answers

  • Here is a sample using your table (the create table part and populating with the data):

    create table GResults (artikelnummer	varchar(20),reparateur varchar(100),
    	 leverancier varchar(100),	artikelomschrijving varchar(100),	locatie varchar(100),	        Totaal int)
    insert into GResults 
    select	 
    '051318A.306',	'TNT SCRAP',	   'Nokia',	   'Backplaine DE34',	   'Auto voorraad Geen',	      2
    union select all
    '051318A.306',	'TNT SCRAP', 	'Nokia',	   'Backplaine DE34',	   'ENN spare lok. Safestore Bergen op Zoom', 1
    union select all
    '051318A.306',	'TNT SCRAP',	   'Nokia',	   'Backplaine DE34',    'ENN spare lok. Shurgard Rotterdam',	  2
    union select all
    '059782A.101',	'Defecte opslag ..',	'Nokia',    'BHXA',	     'ENN spare lok. MiniOpslag Meppel', 	  1
    union select all
    '059782A.101',	'Defecte opslag ..'	,'Nokia'    ,'BHXA'	     ,'ENN spare lok. Shurgard Amersfoort',	  1
    union select all
    '059782A.101',	'Defecte opslag ..' 	,'Nokia'    ,'BHXA'	     ,'ENN spare lok. Shurgard Amsterdam',	  1
    
    
    -- Query starts here. Instead of GResults you can use your current select statement as derived table
    -- e.g. select * from (myCurrentQuery) src PIVOT ...
    
    declare @SQL nvarchar(max), @Cols nvarchar(max)
    select @Cols = STUFF((select ', ' + quotename(Locatie) from (select distinct Locatie from GResults) X 
    for XML PATH('')),1,2,'')
    
    set @SQL = 'select * from GResults PIVOT (max(Totaal) FOR Locatie in (' + @Cols + ')) pvt'
    
    execute (@SQL)
    


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by EricMW Monday, December 6, 2010 2:35 PM
    Monday, December 6, 2010 2:28 PM
    Moderator

All replies

  • Yes this is possible, you need to PIVOT you data, by using PIVOT operator:

    select Number, description, [location1], [location2], [location3], [location4], [location5]
    from (select Number, description, numberatlocation, Name
    	from table1 join table2 on table1.location=table2.code)p
    PIVOT(MAX (numberatlocation) FOR Name IN ( [location1], [location2], [location3], [location4], [location5] )
    ) AS pvt
    ORDER BY Number
    
    I have not tested it, check it and let us know.

     


    ~Manu
    http://sqlwithmanoj.wordpress.com
    Monday, December 6, 2010 11:55 AM

  • create table #t1 (number int,description varchar(20),
                       location int,numberatlocation int)
    create table #t2 (code int,name varchar(20))


    insert into #t1 values (12345,'test',1000,5)
    insert into #t1 values (12345,'test',1001,2)
    insert into #t1 values (12345,'test',1002,4)
    insert into #t1 values (12345,'test',1003,9)
    insert into #t1 values (12345,'test',1004,7)

    insert into #t2 values (1000,'location1')
    insert into #t2 values (1001,'location2')
    insert into #t2 values (1002,'location3')
    insert into #t2 values (1003,'location4')
    insert into #t2 values (1004,'location5')


    select number,
    sum([location1]) as [location1],
    sum([location2]) as [location2],
    sum([location3]) as [location3],
    sum([location4]) as [location4],
    sum([location5]) as [location5]
     from #t1
     join #t2 on #t1.location=#t2.code
     PIVOT

      (
        MAX(numberatlocation)
        FOR name IN([location1], [location2], [location3], [location4], [location5])
      ) AS PVT
    GROUP BY number


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, December 6, 2010 11:59 AM
    Answerer
  • Hi,

    thank you for your quick answer.

    The problem is that the number of locations can vary.

    How do you make that part variable?

    rg.
    Eric

     

    Monday, December 6, 2010 12:01 PM
  • Hi,

    thank you for your quick answer.

    The problem is that the number of locations can vary.

    How do you make that part variable?

    rg.
    Eric

     

    Check following  links to create Dynamic PIVOTs:

    PIVOT SQL: http://jahaines.blogspot.com/2009/08/pivoting-data-using-sql-2005-part-1.html
    Dynamic PIVOT SQL: http://jahaines.blogspot.com/2009/08/pivoting-data-using-sql-2005-part-2.html
    Completely Dynamic SQL: http://jahaines.blogspot.com/2009/08/pivoting-data-using-sql-2005-part-3.html


    ~Manu
    http://sqlwithmanoj.wordpress.com
    Monday, December 6, 2010 12:05 PM
  • See Itzik's example of dynamic pivot

    CREATE TABLE Foo (
      foo_type CHAR(1) PRIMARY KEY,
      foo_value INT);

    INSERT INTO Foo VALUES('A', 1);
    INSERT INTO Foo VALUES('B', 2);
    INSERT INTO Foo VALUES('C', 3);
    INSERT INTO Foo VALUES('D', 4);

    -- pivot using CASE
    SELECT MAX(CASE WHEN foo_type = 'A' THEN foo_value END) AS A,
            MAX(CASE WHEN foo_type = 'B' THEN foo_value END) AS B,
            MAX(CASE WHEN foo_type = 'C' THEN foo_value END) AS C,
            MAX(CASE WHEN foo_type = 'D' THEN foo_value END) AS D
    FROM Foo;

    -- pivot using PIVOT operator (SQL Server 2005/2008)
    SELECT A, B, C, D
    FROM Foo
    PIVOT
    (MAX(foo_value) FOR foo_type IN (A, B, C, D)) AS P;

    -- dynamic pivot (SQL Server 2005/2008)
    DECLARE @pivot_cols NVARCHAR(1000);
    SELECT @pivot_cols =
            STUFF((SELECT DISTINCT '],[' + foo_type
                   FROM Foo
                   ORDER BY '],[' + foo_type
                   FOR XML PATH('')
                   ), 1, 2, '') + ']';

    DECLARE @pivot_query NVARCHAR(2000);
    SET @pivot_query =
    N'SELECT ' + @pivot_cols +
      'FROM Foo ' +
      'PIVOT ' +
      '(MAX(foo_value) FOR foo_type IN (' + @pivot_cols + ')) AS P;';

    EXEC(@pivot_query);

    DROP TABLE Foo;
    ---------------------------------------


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, December 6, 2010 12:12 PM
    Answerer
  • Hi,

    thank you for your quick answer.

    The problem is that the number of locations can vary.

    How do you make that part variable?

    rg.
    Eric

     

    Try this:

     

    declare @col varchar(1000)
    declare @sql varchar(2000)
    
    select @col = COALESCE(@col + ', ','') + QUOTENAME(name)
    from table2
    
    set @sql = '
    select number, description, ' + @col + '
    from (select number, description, numberatlocation, name
    	from table1 join table2 on table1.location=table2.code)p
    PIVOT(MAX (numberatlocation) FOR Name IN (' + @col + ')
    ) AS pvt
    ORDER BY number'
    
    print @sql
    
    exec (@sql)
    

     


    ~Manu
    http://sqlwithmanoj.wordpress.com
    Monday, December 6, 2010 12:14 PM
  • hi Eric, using Case you can do this but according to my knoladge best method is PIVOT(Manu's post & Uri damant posts) if you have SQL 2000 this will help you.

    ---

    SELECT Table1.number ,Table1.[description]
        ,MAX(CASE WHEN Table2.name='location1' THEN Table1.numberatlocation END) location1
        ,MAX(CASE WHEN Table2.name='location2' THEN Table1.numberatlocation END) location2
        ,MAX(CASE WHEN Table2.name='location3' THEN Table1.numberatlocation END) location3
        ,MAX(CASE WHEN Table2.name='location4' THEN Table1.numberatlocation END) location4
        ,MAX(CASE WHEN Table2.name='location5' THEN Table1.numberatlocation END) location5
    FROM
    (
        SELECT 12345 number, 'test' [description], 1000 location,5 numberatlocation
        UNION ALL  
        SELECT 12345 number, 'test' [description], 1001 location,2 numberatlocation
        UNION ALL  
        SELECT 12345 number, 'test' [description], 1002 location,4 numberatlocation
        UNION ALL  
        SELECT 12345 number, 'test' [description], 1003 location,9 numberatlocation
        UNION ALL  
        SELECT 12345 number, 'test' [description], 1004 location,7 numberatlocation
    )  Table1 INNER JOIN 

    (
        SELECT 1000 code,   'location1' name
        UNION ALL
        SELECT 1001 code,   'location2' name
        UNION ALL
        SELECT 1002 code,   'location3' name
        UNION ALL
        SELECT 1003 code,   'location4' name
        UNION ALL
        SELECT 1004 code,   'location5' name
    ) Table2 ON Table1.location=Table2.code
    GROUP BY Table1.number ,Table1.[description]

    ---

    Thanks

    Tharindu Dhaneenja


    Tharindu Dhaneenja http://dhaneenja.blogspot.com

    Monday, December 6, 2010 12:16 PM
  • Thank you all for helping.

    Its a bit to complex for my understanding.

    Select t1.artikelnummer, t2.naam as reparateur, t3.naam as leverancier, t1.artikelomschrijving, t5.naam as locatie, t4.Aanwezig as Totaal
     from Spares as t1 left join Reparateur as t2 on t1.Repair = t2.Code left join Leveranciers as t3 on t1.Leverancier = t3.Code
    left join Voorraad as t4 on t1.Code = t4.Artikel left join Locations as t5 on t4.Locatie = t5.Code 
    group by t1.Artikelnummer,t2.Naam,t3.Naam, t1.ArtikelOmschrijving, t5.naam, t4.Aanwezig 
    having t4.Aanwezig > 0
    

    The code above produces the following table:

    artikelnummer	reparateur	 leverancier	artikelomschrijving	locatie	                Totaal
    051318A.306	TNT SCRAP	     Nokia	     Backplaine DE34	     Auto voorraad Geen	            2
    051318A.306	TNT SCRAP 	Nokia	     Backplaine DE34	     ENN spare lok. Safestore Bergen op Zoom  1
    051318A.306	TNT SCRAP	     Nokia	     Backplaine DE34       ENN spare lok. Shurgard Rotterdam	   2
    059782A.101	Defecte opslag ..	Nokia       BHXA	         ENN spare lok. MiniOpslag Meppel 	   1
    059782A.101	Defecte opslag ..	Nokia       BHXA	         ENN spare lok. Shurgard Amersfoort	   1
    059782A.101	Defecte opslag .. 	Nokia       BHXA	         ENN spare lok. Shurgard Amsterdam	   1

    There about 30 locations.

    I tried to understand the code in the provided links, but I'm having a hard time translating it to my specific situation.
    I could just use the above table and then sort it out in my visual basic .net program, but that will take up more time.

    Any more help is highly appriciated.

    rg,
    Eric

    Monday, December 6, 2010 1:12 PM
  • Hi Eric, try this solution please :

    --------------------------------------------------------------
    -----------------------------TABLES--------------------------
    --------------------------------------------------------------
    Create Table TAB1 (number int,[description] varchar(10),location int,numberatlocation int)
    insert TAB1
    select 12345,'test',1000,5 UNION ALL
    select 12345,'test',1001,2 UNION ALL
    select 12345,'test',1002,4 UNION ALL
    select 12345,'test',1003,9 UNION ALL
    select 12345,'test',1004,7
    Create Table TAB2 (code int,[name] varchar(20))
    insert TAB2 
    select 1000,'location1' UNION ALL
    select 1001,'location2' UNION ALL
    select 1002,'location3' UNION ALL
    select 1003,'location4' UNION ALL
    select 1004,'location5' 
    --------------------------------------------------------------
    -----------------------------QUERY----------------------------
    --------------------------------------------------------------
    declare @str varchar(MAX)
    declare @sql varchar(MAX)
    set @str=(SELECT Top 1
    substring((SELECT ( ' [' + [name] + '],') 
    FROM TAB2 t2 
    FOR XML PATH( '' ) 
    ), 2, 1000 ) as Loc
    FROM TAB2 t1)
    set @str=LEFT(@str,LEN(@str)-1)
    set @sql='select Number, description,'+@str+
    ' from (select T1.Number, T1.description, T1.numberatlocation, T2.Name
    from TAB1 T1 join tAB2 T2 on T1.location=T2.code) p
    PIVOT(MAX (numberatlocation) FOR Name IN ('+@str+')) AS pvt
    ORDER BY Number'
    exec (@sql)
    --------------------------------------------------------------
    -----------------------------RESULTS--------------------------
    --------------------------------------------------------------
    --12345 test 5 2 4 9 7
    --------------------------------------------------------------
    Drop Table TAB1
    Drop Table TAB2
    

    Best regards
    Monday, December 6, 2010 1:47 PM
  • Here is a sample using your table (the create table part and populating with the data):

    create table GResults (artikelnummer	varchar(20),reparateur varchar(100),
    	 leverancier varchar(100),	artikelomschrijving varchar(100),	locatie varchar(100),	        Totaal int)
    insert into GResults 
    select	 
    '051318A.306',	'TNT SCRAP',	   'Nokia',	   'Backplaine DE34',	   'Auto voorraad Geen',	      2
    union select all
    '051318A.306',	'TNT SCRAP', 	'Nokia',	   'Backplaine DE34',	   'ENN spare lok. Safestore Bergen op Zoom', 1
    union select all
    '051318A.306',	'TNT SCRAP',	   'Nokia',	   'Backplaine DE34',    'ENN spare lok. Shurgard Rotterdam',	  2
    union select all
    '059782A.101',	'Defecte opslag ..',	'Nokia',    'BHXA',	     'ENN spare lok. MiniOpslag Meppel', 	  1
    union select all
    '059782A.101',	'Defecte opslag ..'	,'Nokia'    ,'BHXA'	     ,'ENN spare lok. Shurgard Amersfoort',	  1
    union select all
    '059782A.101',	'Defecte opslag ..' 	,'Nokia'    ,'BHXA'	     ,'ENN spare lok. Shurgard Amsterdam',	  1
    
    
    -- Query starts here. Instead of GResults you can use your current select statement as derived table
    -- e.g. select * from (myCurrentQuery) src PIVOT ...
    
    declare @SQL nvarchar(max), @Cols nvarchar(max)
    select @Cols = STUFF((select ', ' + quotename(Locatie) from (select distinct Locatie from GResults) X 
    for XML PATH('')),1,2,'')
    
    set @SQL = 'select * from GResults PIVOT (max(Totaal) FOR Locatie in (' + @Cols + ')) pvt'
    
    execute (@SQL)
    


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by EricMW Monday, December 6, 2010 2:35 PM
    Monday, December 6, 2010 2:28 PM
    Moderator
  • Thank you very much, Naomi. You are my hero.

    Everybody who replied here, thank you too!

     

    rg,
    Eric

    Monday, December 6, 2010 2:37 PM