I need help with a SQL Server 2008 Query

Hi Experts I need help with a SQL Server 2008 query.

Below is a sample of my Table. It only has two colums.  Below that I have placed a sample of what I want my query to return.
Please show me how to write that query or queries to gain the final result.  Thank you very much in advance.

Original table:
Original Table
What I want the query to return:
Final result

Thank you very much in advance,
mrotor
mainrotorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John TsioumprisSoftware & Systems EngineerCommented:
If you use Access then you can use this query
TRANSFORM First(Table1.ETA) AS FirstOfETA
SELECT Table1.ItmNumber
FROM Table1
GROUP BY Table1.ItmNumber
ORDER BY Table1.ItmNumber
PIVOT Table1.ETA;

Open in new window


I will try to convert it to SQL later but now i don't have any SQL server available
PortletPaulEE Topic AdvisorCommented:
In future please provide sample data in a reusable format (simple text like the following is fine), don't forget to include the tablename too.
MyTable
Itemnumber  ETA
abc123            7/11/2015
abc123            8/11/2015
abc123            9/11/2015
abc123           10/11/2015
abc123            11/11/2015

Open in new window


anyway, this will match the expected result in SQL Serever

select
        Itemnumber
      , max(case when rn = 1 then ETA end) as ETA1
      , max(case when rn = 2 then ETA end) as ETA2
      , max(case when rn = 3 then ETA end) as ETA3
      , max(case when rn = 4 then ETA end) as ETA4
      , max(case when rn = 5 then ETA end) as ETA5
from (
      select Itemnumber
             , ETA
             , row_number() over(partition by Itemnumber
                                 order by ETA ASC) AS rn
       from MyTable
     ) as x
group by
        Itemnumber

Open in new window

John TsioumprisSoftware & Systems EngineerCommented:
Well i have a "solution" for SQL but is not perfect since it requires some Dynamic SQL but it will give you a starting point....i was forced to do some 'convertions' due to lack of time

CREATE TABLE pivottest (
  ITEMNUMBER varchar(25)  NOT NULL,
  ETA BIGINT NOT NULL,
  PRIMARY KEY (ITEMNUMBER, ETA)
)



INSERT INTO pivottest(ITEMNUMBER, ETA) VALUES
('ABC123', '20150710');
INSERT INTO pivottest(ITEMNUMBER, ETA) VALUES
('ABC123', '20150715');
INSERT INTO pivottest(ITEMNUMBER, ETA) VALUES
('DEF123', '20150715');
INSERT INTO pivottest(ITEMNUMBER, ETA) VALUES
('DEF123', '20150724');

Open in new window


SELECT * FROM pivottest
PIVOT (AVG([ETA]) FOR ETA In ([20150710],[20150715])) as xx

Open in new window


You can see a SQLFiddle here
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Scott PletcherSenior DBACommented:
I had a different take than others on what the requirement is here.  I believe you want each unique date to have its own column, and a given item_number only uses that column if that date appears for it.

SELECT
    tn.ITEM_NUMBER,
    MAX(CASE WHEN ETA# = 1 THEN tn.ETA END) AS ETA1,
    MAX(CASE WHEN ETA# = 2 THEN tn.ETA END) AS ETA2,
    MAX(CASE WHEN ETA# = 3 THEN tn.ETA END) AS ETA3,
    MAX(CASE WHEN ETA# = 4 THEN tn.ETA END) AS ETA4,
    MAX(CASE WHEN ETA# = 5 THEN tn.ETA END) AS ETA5,
    MAX(CASE WHEN ETA# = 6 THEN tn.ETA END) AS ETA6
    ,... ETA# = 7 ...more #s if/as needed
FROM #table_name tn
INNER JOIN (
    SELECT ETA, ROW_NUMBER() OVER(ORDER BY ETA) AS ETA#
    FROM (
        SELECT DISTINCT ETA
        FROM #table_name
    ) AS derived
) AS ETA_List ON
    ETA_List.ETA = tn.ETA
GROUP BY tn.ITEM_NUMBER


Please post sample data in a directly query-able format, like below ... it literally took me longer to set up the sample data than to write the result query:

CREATE TABLE #table_name (
  ITEM_NUMBER varchar(25)  NOT NULL,
  ETA date NULL,
  UNIQUE CLUSTERED ( ITEM_NUMBER, ETA ) --specifying constraints/indexes is optional of course :)
)

INSERT INTO #table_name
SELECT 'ABC123', '7/15/2015' UNION ALL
SELECT 'ABC123', '10/12/2015' UNION ALL
SELECT 'ABC123', '11/10/2015' UNION ALL
SELECT 'DEF456', '9/24/2015' UNION ALL
SELECT 'DEF456', '10/12/2015' UNION ALL
SELECT 'GHI789', '7/22/2015' UNION ALL
SELECT 'JKL012', '10/12/2015' UNION ALL
SELECT 'JKL012', '11/10/2015' UNION ALL
SELECT 'XYZ121', '7/15/2015'

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mainrotorAuthor Commented:
tsgiannis,
In your sample, would I have to type in every possible ETA date in the following query?

SELECT * FROM pivottest
PIVOT (AVG([ETA]) FOR ETA In ([20150710],[20150715])) as xx


mrotor
Scott PletcherSenior DBACommented:
Not sure what you mean -- my query outputs the exact format specified in the original q.
PortletPaulEE Topic AdvisorCommented:
mmm, I see Scott has it and I missed it.
It is necessary to align each date to a column number to achieve the desired result.
Vitor MontalvãoMSSQL Senior EngineerCommented:
mainrotor, do you still need help with this question?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.