CustStateTable

CustID         StateCode
-------        ---------
abc              1
abc              2
abc              3 

or 

CustStateTable

CustID         StateCode
-------        ---------
xyz              9
xyz              7


What I would like to to put all on one row.... so the results would look like this

CustStateTable

CustID         State_1     State_2     State_3
-------        ---------   -------     -------
abc              1            2           3

or 

CustID         State_1     State_2     State_3
-------        ---------   -------     -------
xyz               9           7        null


 Sure it is simple to do but all the reading I have done I cannot put my finger one where to start.

Any Pointers?

Thanks,

quote:

---

Originally posted by jdaman

Do you have some way to tie a record set to a specific State_1, State_2, State_3?

---

CustID         StateCode           CityCode         TypeCode
-------        ---------           --------         --------
abc              1                    aa               zz
abc              2                    bb               yy
abc              3                    cc               xx


What I am trying to return is 

CustID     State_1   State_2     State_3    City_1   City_2    City_3  Type_1   Type_2  Type_3
-------    -------   -------     -------    ------   ------    ------  ------   ------  ------
abc              1      2           3          aa      bb         cc    zz        yy       xx

DECLARE @CustStateTable TABLE ( CustID CHAR(3), StateCode INT )

INSERT @CustStateTable ( CustID, StateCode )
SELECT 'abc', 1 UNION ALL
SELECT 'abc', 2 UNION ALL
SELECT 'abc', 3 UNION ALL
SELECT 'xyz', 9 UNION ALL
SELECT 'xyz', 7

SELECT * FROM (
SELECT ROW_NUMBER() OVER(PARTITION BY CustId ORDER BY StateCode) RowNumber, CustId, StateCode FROM @CustStateTable ) a
PIVOT (SUM(StateCode) FOR RowNumber IN ( [1], [2], [3] )) p

quote:

---

Originally posted by nathans

Is it only ever 3?

You still didnt show how StateCode = 9 is related to State_1 .



Nathan Skerl

---

DECLARE @CustStateTable TABLE ( CustID CHAR(3), StateCode INT, CityCode CHAR(10), TypeCode CHAR(2) )

INSERT @CustStateTable ( CustID, StateCode, CityCode, TypeCode )
SELECT 'abc', 1, 'Seattle', 'AA' UNION ALL
SELECT 'abc', 2, 'Pensacola', 'AB' UNION ALL
SELECT 'abc', 3, 'Dallas', 'CF' UNION ALL
SELECT 'xyz', 9, 'Boston', 'AA' UNION ALL
SELECT 'xyz', 7, 'Portland', 'RF'

SELECT CustID,
       MAX(TypeCode1) AS TypeCode1,
       MAX(TypeCode2) AS TypeCode2,
       MAX(TypeCode3) AS TypeCode3,
       MAX(StateCode1) AS StateCode1,
       MAX(StateCode2) AS StateCode2,
       MAX(StateCode3) AS StateCode3,
       MAX([1]) AS CityCode1,
       MAX([2]) AS CityCode2,
       MAX([3]) AS CityCode3
FROM (
    SELECT ROW_NUMBER() OVER(PARTITION BY CustID ORDER BY COALESCE([1], [2], [3])) RowNumber,
           CustID,
           CityCode,
           [1] AS TypeCode1,
           [2] AS TypeCode2,
           [3] AS TypeCode3,
           StateCode1,
           StateCode2,
           StateCode3
    FROM (
        SELECT ROW_NUMBER() OVER(PARTITION BY CustID ORDER COALESCE([1], [2], [3])) RowNumber,
               CustID,
               CityCode,
               TypeCode,
               [1] AS StateCode1,
               [2] AS StateCode2,
               [3] AS StateCode3
        FROM (
            SELECT ROW_NUMBER() OVER(PARTITION BY CustID ORDER BY StateCode) RowNumber,
                   CustID,
                   StateCode,
                   CityCode,
                   TypeCode
            FROM @CustStateTable ) a
        PIVOT (SUM(StateCode) FOR RowNumber IN ( [1], [2], [3] )) p ) a
    PIVOT (MAX(TypeCode) FOR RowNumber IN ( [1], [2], [3] )) p ) a
PIVOT (MAX(CityCode) FOR RowNumber IN ( [1], [2], [3] )) p
GROUP BY CustID

DECLARE @CustStateTable TABLE ( i INT IDENTITY, CustID CHAR(3), StateCode INT, CityCode CHAR(10), TypeCode CHAR(2) )

INSERT @CustStateTable ( CustID, StateCode, CityCode, TypeCode )
SELECT 'abc', 1, 'Seattle', 'AA' UNION ALL
SELECT 'abc', 2, 'Pensacola', 'AB' UNION ALL
SELECT 'abc', 3, 'Dallas', 'CF' UNION ALL
SELECT 'xyz', 9, 'Boston', 'AA' UNION ALL
SELECT 'xyz', 7, 'Portland', 'RF'

SELECT CustID,
       MAX(TypeCode1) AS TypeCode1,
       MAX(TypeCode2) AS TypeCode2,
       MAX(TypeCode3) AS TypeCode3,
       MAX(StateCode1) AS StateCode1,
       MAX(StateCode2) AS StateCode2,
       MAX(StateCode3) AS StateCode3,
       MAX([1]) AS CityCode1,
       MAX([2]) AS CityCode2,
       MAX([3]) AS CityCode3
FROM (
    SELECT ROW_NUMBER() OVER(PARTITION BY CustID ORDER BY i) RowNumber,
           CustID,
           CityCode,
           [1] AS TypeCode1,
           [2] AS TypeCode2,
           [3] AS TypeCode3,
           StateCode1,
           StateCode2,
           StateCode3
    FROM (
        SELECT ROW_NUMBER() OVER(PARTITION BY CustID ORDER BY i) RowNumber,
               i,
               CustID,
               CityCode,
               TypeCode,
               [1] AS StateCode1,
               [2] AS StateCode2,
               [3] AS StateCode3
        FROM (
            SELECT ROW_NUMBER() OVER(PARTITION BY CustID ORDER BY i) RowNumber,
                   i,
                   CustID,
                   StateCode,
                   CityCode,
                   TypeCode
            FROM @CustStateTable ) a
        PIVOT (SUM(StateCode) FOR RowNumber IN ( [1], [2], [3] )) p ) a
    PIVOT (MAX(TypeCode) FOR RowNumber IN ( [1], [2], [3] )) p ) a
PIVOT (MAX(CityCode) FOR RowNumber IN ( [1], [2], [3] )) p
GROUP BY CustID

quote:

---

Originally posted by chrispy

jdaman,

I think somewhere in there the solution is. I need to look at it some more.

One thing worth mentioning the all the codes I mentioned I am getting from a lookup table so there could be more then 50 of each.

---

declare @TestTable table (ProductID char(3), StateCode int)
insert into @TestTable
	select 'abc', 1 union
	select 'abc', 2 union
	select 'abc', 3 union
	select 'xyz', 9 union
	select 'xyz', 7

select	a.ProductID,
	min(a.StateCode) [State_1],
	min(b.StateCode) [State_2],
	min(c.StateCode) [State_3]
from	@TestTable a
left
join	@TestTable b on
	a.ProductID = b.ProductID and
	a.StateCode < b.StateCode
left
join	@TestTable c on
	b.ProductID = c.ProductID and
	b.StateCode < c.StateCode and
	a.StateCode < c.StateCode 
group
by	a.ProductID

CustID         State_1     State_2     State_3
-------        ---------   -------     -------
abc              1            2           3

CustID         State_1     State_2     State_3
-------        ---------   -------     -------
xyz               9           7        null

DECLARE @CustStateTable TABLE ( i INT IDENTITY, CustID CHAR(3), StateCode INT, CityCode CHAR(10), TypeCode CHAR(2) )

INSERT @CustStateTable ( CustID, StateCode, CityCode, TypeCode )
SELECT 'abc', 1, 'Seattle', 'AA' UNION ALL
SELECT 'abc', 2, 'Pensacola', 'AB' UNION ALL
SELECT 'abc', 3, 'Dallas', 'CF' UNION ALL
SELECT 'xyz', 9, 'Boston', 'AA' UNION ALL
SELECT 'xyz', 7, 'Portland', 'RF'

SELECT CustID,
       MAX(TypeCode1) AS TypeCode1, MAX(TypeCode2) AS TypeCode2, MAX(TypeCode3) AS TypeCode3,
       MAX(TypeCode4) AS TypeCode4, MAX(TypeCode5) AS TypeCode5, MAX(TypeCode6) AS TypeCode6,
       MAX(TypeCode7) AS TypeCode7, MAX(TypeCode8) AS TypeCode8, MAX(TypeCode9) AS TypeCode9,
       MAX(TypeCode10) AS TypeCode10, MAX(TypeCode11) AS TypeCode11, MAX(TypeCode12) AS TypeCode12,
       MAX(TypeCode13) AS TypeCode13, MAX(TypeCode14) AS TypeCode14, MAX(TypeCode15) AS TypeCode15,
       MAX(TypeCode16) AS TypeCode16, MAX(TypeCode17) AS TypeCode17, MAX(TypeCode18) AS TypeCode18,
       MAX(TypeCode19) AS TypeCode19, MAX(TypeCode20) AS TypeCode20, MAX(TypeCode21) AS TypeCode21,
       MAX(TypeCode22) AS TypeCode22, MAX(TypeCode23) AS TypeCode23, MAX(TypeCode24) AS TypeCode24,
       MAX(TypeCode25) AS TypeCode25, MAX(TypeCode26) AS TypeCode26, MAX(TypeCode27) AS TypeCode27,
       MAX(TypeCode28) AS TypeCode28, MAX(TypeCode29) AS TypeCode29, MAX(TypeCode30) AS TypeCode30,
       MAX(TypeCode31) AS TypeCode31, MAX(TypeCode32) AS TypeCode32, MAX(TypeCode33) AS TypeCode33,
       MAX(TypeCode34) AS TypeCode34, MAX(TypeCode35) AS TypeCode35, MAX(TypeCode36) AS TypeCode36,
       MAX(TypeCode37) AS TypeCode37, MAX(TypeCode38) AS TypeCode38, MAX(TypeCode39) AS TypeCode39,
       MAX(TypeCode40) AS TypeCode40, MAX(TypeCode41) AS TypeCode41, MAX(TypeCode42) AS TypeCode42,
       MAX(TypeCode43) AS TypeCode43, MAX(TypeCode44) AS TypeCode44, MAX(TypeCode45) AS TypeCode45,
       MAX(TypeCode46) AS TypeCode46, MAX(TypeCode47) AS TypeCode47, MAX(TypeCode48) AS TypeCode48,
       MAX(TypeCode49) AS TypeCode49, MAX(TypeCode50) AS TypeCode50, MAX(TypeCode51) AS TypeCode51,
       MAX(TypeCode52) AS TypeCode52, MAX(TypeCode53) AS TypeCode53, MAX(TypeCode54) AS TypeCode54,
       MAX(TypeCode55) AS TypeCode55, MAX(TypeCode56) AS TypeCode56, MAX(TypeCode57) AS TypeCode57,
       MAX(TypeCode58) AS TypeCode58, MAX(TypeCode59) AS TypeCode59, MAX(TypeCode60) AS TypeCode60,
       MAX(TypeCode61) AS TypeCode61,
       MAX(StateCode1) AS StateCode1, MAX(StateCode2) AS StateCode2, MAX(StateCode3) AS StateCode3,
       MAX(StateCode4) AS StateCode4, MAX(StateCode5) AS StateCode5, MAX(StateCode6) AS StateCode6,
       MAX(StateCode7) AS StateCode7, MAX(StateCode8) AS StateCode8, MAX(StateCode9) AS StateCode9,
       MAX(StateCode10) AS StateCode10, MAX(StateCode11) AS StateCode11, MAX(StateCode12) AS StateCode12,
       MAX(StateCode13) AS StateCode13, MAX(StateCode14) AS StateCode14, MAX(StateCode15) AS StateCode15,
       MAX(StateCode16) AS StateCode16, MAX(StateCode17) AS StateCode17, MAX(StateCode18) AS StateCode18,
       MAX(StateCode19) AS StateCode19, MAX(StateCode20) AS StateCode20, MAX(StateCode21) AS StateCode21,
       MAX(StateCode22) AS StateCode22, MAX(StateCode23) AS StateCode23, MAX(StateCode24) AS StateCode24,
       MAX(StateCode25) AS StateCode25, MAX(StateCode26) AS StateCode26, MAX(StateCode27) AS StateCode27,
       MAX(StateCode28) AS StateCode28, MAX(StateCode29) AS StateCode29, MAX(StateCode30) AS StateCode30,
       MAX(StateCode31) AS StateCode31, MAX(StateCode32) AS StateCode32, MAX(StateCode33) AS StateCode33,
       MAX(StateCode34) AS StateCode34, MAX(StateCode35) AS StateCode35, MAX(StateCode36) AS StateCode36,
       MAX(StateCode37) AS StateCode37, MAX(StateCode38) AS StateCode38, MAX(StateCode39) AS StateCode39,
       MAX(StateCode40) AS StateCode40, MAX(StateCode41) AS StateCode41, MAX(StateCode42) AS StateCode42,
       MAX(StateCode43) AS StateCode43, MAX(StateCode44) AS StateCode44, MAX(StateCode45) AS StateCode45,
       MAX(StateCode46) AS StateCode46, MAX(StateCode47) AS StateCode47, MAX(StateCode48) AS StateCode48,
       MAX(StateCode49) AS StateCode49, MAX(StateCode50) AS StateCode50, MAX(StateCode51) AS StateCode51,
       MAX(StateCode52) AS StateCode52, MAX(StateCode53) AS StateCode53, MAX(StateCode54) AS StateCode54,
       MAX(StateCode55) AS StateCode55, MAX(StateCode56) AS StateCode56, MAX(StateCode57) AS StateCode57,
       MAX(StateCode58) AS StateCode58, MAX(StateCode59) AS StateCode59, MAX(StateCode60) AS StateCode60,
       MAX(StateCode61) AS StateCode61,
       MAX([1]) AS CityCode1, MAX([2]) AS CityCode2, MAX([3]) AS CityCode3,
       MAX([4]) AS CityCode4, MAX([5]) AS CityCode5, MAX([6]) AS CityCode6,
       MAX([7]) AS CityCode7, MAX() AS CityCode8, MAX([9]) AS CityCode9,
       MAX([10]) AS CityCode10, MAX([11]) AS CityCode11, MAX([12]) AS CityCode12,
       MAX([13]) AS CityCode13, MAX([14]) AS CityCode14, MAX([15]) AS CityCode15,
       MAX([16]) AS CityCode16, MAX([17]) AS CityCode17, MAX([18]) AS CityCode18,
       MAX([19]) AS CityCode19, MAX([20]) AS CityCode20, MAX([21]) AS CityCode21,
       MAX([22]) AS CityCode22, MAX([23]) AS CityCode23, MAX([24]) AS CityCode24,
       MAX([25]) AS CityCode25, MAX([26]) AS CityCode26, MAX([27]) AS CityCode27,
       MAX([28]) AS CityCode28, MAX([29]) AS CityCode29, MAX([30]) AS CityCode30,
       MAX([31]) AS CityCode31, MAX([32]) AS CityCode32, MAX([33]) AS CityCode33,
       MAX([34]) AS CityCode34, MAX([35]) AS CityCode35, MAX([36]) AS CityCode36,
       MAX([37]) AS CityCode37, MAX([38]) AS CityCode38, MAX([39]) AS CityCode39,
       MAX([40]) AS CityCode40, MAX([41]) AS CityCode41, MAX([42]) AS CityCode42,
       MAX([43]) AS CityCode43, MAX([44]) AS CityCode44, MAX([45]) AS CityCode45,
       MAX([46]) AS CityCode46, MAX([47]) AS CityCode47, MAX([48]) AS CityCode48,
       MAX([49]) AS CityCode49, MAX([50]) AS CityCode50, MAX([51]) AS CityCode51,
       MAX([52]) AS CityCode52, MAX([53]) AS CityCode53, MAX([54]) AS CityCode54,
       MAX([55]) AS CityCode55, MAX([56]) AS CityCode56, MAX([57]) AS CityCode57,
       MAX([58]) AS CityCode58, MAX([59]) AS CityCode59, MAX([60]) AS CityCode60,
       MAX([61]) AS CityCode61
FROM (
    SELECT ROW_NUMBER() OVER(PARTITION BY CustID ORDER BY i) RowNumber,
           CustID,
           CityCode,
           [1] AS TypeCode1, [2] AS TypeCode2, [3] AS TypeCode3,
           [4] AS TypeCode4, [5] AS TypeCode5, [6] AS TypeCode6,
           [7] AS TypeCode7,  AS TypeCode8, [9] AS TypeCode9,
           [10] AS TypeCode10, [11] AS TypeCode11, [12] AS TypeCode12,
           [13] AS TypeCode13, [14] AS TypeCode14, [15] AS TypeCode15,
           [16] AS TypeCode16, [17] AS TypeCode17, [18] AS TypeCode18,
           [19] AS TypeCode19, [20] AS TypeCode20, [21] AS TypeCode21,
           [22] AS TypeCode22, [23] AS TypeCode23, [24] AS TypeCode24,
           [25] AS TypeCode25, [26] AS TypeCode26, [27] AS TypeCode27,
           [28] AS TypeCode28, [29] AS TypeCode29, [30] AS TypeCode30,
           [31] AS TypeCode31, [32] AS TypeCode32, [33] AS TypeCode33,
           [34] AS TypeCode34, [35] AS TypeCode35, [36] AS TypeCode36,
           [37] AS TypeCode37, [38] AS TypeCode38, [39] AS TypeCode39,
           [40] AS TypeCode40, [41] AS TypeCode41, [42] AS TypeCode42,
           [43] AS TypeCode43, [44] AS TypeCode44, [45] AS TypeCode45,
           [46] AS TypeCode46, [47] AS TypeCode47, [48] AS TypeCode48,
           [49] AS TypeCode49, [50] AS TypeCode50, [51] AS TypeCode51,
           [52] AS TypeCode52, [53] AS TypeCode53, [54] AS TypeCode54,
           [55] AS TypeCode55, [56] AS TypeCode56, [57] AS TypeCode57,
           [58] AS TypeCode58, [59] AS TypeCode59, [60] AS TypeCode60,
           [61] AS TypeCode61,
           StateCode1, StateCode2, StateCode3,
           StateCode4, StateCode5, StateCode6,
           StateCode7, StateCode8, StateCode9,
           StateCode10, StateCode11, StateCode12,
           StateCode13, StateCode14, StateCode15,
           StateCode16, StateCode17, StateCode18,
           StateCode19, StateCode20, StateCode21,
           StateCode22, StateCode23, StateCode24,
           StateCode25, StateCode26, StateCode27,
           StateCode28, StateCode29, StateCode30,
           StateCode31, StateCode32, StateCode33,
           StateCode34, StateCode35, StateCode36,
           StateCode37, StateCode38, StateCode39,
           StateCode40, StateCode41, StateCode42,
           StateCode43, StateCode44, StateCode45,
           StateCode46, StateCode47, StateCode48,
           StateCode49, StateCode50, StateCode51,
           StateCode52, StateCode53, StateCode54,
           StateCode55, StateCode56, StateCode57,
           StateCode58, StateCode59, StateCode60,
           StateCode61

    FROM (
        SELECT ROW_NUMBER() OVER(PARTITION BY CustID ORDER BY i) RowNumber,
               i,
               CustID,
               CityCode,
               TypeCode,
               [1] AS StateCode1, [2] AS StateCode2, [3] AS StateCode3,
               [4] AS StateCode4, [5] AS StateCode5, [6] AS StateCode6,
               [7] AS StateCode7,  AS StateCode8, [9] AS StateCode9,
               [10] AS StateCode10, [11] AS StateCode11, [12] AS StateCode12,
               [13] AS StateCode13, [14] AS StateCode14, [15] AS StateCode15,
               [16] AS StateCode16, [17] AS StateCode17, [18] AS StateCode18,
               [19] AS StateCode19, [20] AS StateCode20, [21] AS StateCode21,
               [22] AS StateCode22, [23] AS StateCode23, [24] AS StateCode24,
               [25] AS StateCode25, [26] AS StateCode26, [27] AS StateCode27,
               [28] AS StateCode28, [29] AS StateCode29, [30] AS StateCode30,
               [31] AS StateCode31, [32] AS StateCode32, [33] AS StateCode33,
               [34] AS StateCode34, [35] AS StateCode35, [36] AS StateCode36,
               [37] AS StateCode37, [38] AS StateCode38, [39] AS StateCode39,
               [40] AS StateCode40, [41] AS StateCode41, [42] AS StateCode42,
               [43] AS StateCode43, [44] AS StateCode44, [45] AS StateCode45,
               [46] AS StateCode46, [47] AS StateCode47, [48] AS StateCode48,
               [49] AS StateCode49, [50] AS StateCode50, [51] AS StateCode51,
               [52] AS StateCode52, [53] AS StateCode53, [54] AS StateCode54,
               [55] AS StateCode55, [56] AS StateCode56, [57] AS StateCode57,
               [58] AS StateCode58, [59] AS StateCode59, [60] AS StateCode60,
               [61] AS StateCode61
        FROM (
            SELECT ROW_NUMBER() OVER(PARTITION BY CustID ORDER BY i) RowNumber,
                   i,
                   CustID,
                   StateCode,
                   CityCode,
                   TypeCode
            FROM @CustStateTable ) a
        PIVOT (SUM(StateCode) FOR RowNumber IN ( [1], [2], [3], [4], [5], [6], [7], , [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31], [32], [33], [34], [35], [36], [37], [38], [39], [40], [41], [42], [43], [44], [45], [46], [47], [48], [49], [50], [51], [52], [53], [54], [55], [56], [57], [58], [59], [60], [61] )) p ) a
    PIVOT (MAX(TypeCode) FOR RowNumber IN ( [1], [2], [3], [4], [5], [6], [7], , [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31], [32], [33], [34], [35], [36], [37], [38], [39], [40], [41], [42], [43], [44], [45], [46], [47], [48], [49], [50], [51], [52], [53], [54], [55], [56], [57], [58], [59], [60], [61] )) p ) a
PIVOT (MAX(CityCode) FOR RowNumber IN ( [1], [2], [3], [4], [5], [6], [7], , [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31], [32], [33], [34], [35], [36], [37], [38], [39], [40], [41], [42], [43], [44], [45], [46], [47], [48], [49], [50], [51], [52], [53], [54], [55], [56], [57], [58], [59], [60], [61] )) p
GROUP BY CustID

quote:

---

Originally posted by Peso

Why so many derived table?
What is wrong with my simple suggestion post 01/22/2008 : 18:56:36 ?

---

E 12°55'05.25"
N 56°04'39.16"

---

quote:

---

Originally posted by Peso

As in this post 01/23/2008 : 11:37:41 ?

---

E 12°55'05.25"
N 56°04'39.16"

---