Tall and skinny to short and fat

  • I have this table multiple rows that I have to stretch out to one long row, insurance-related. Each policy has a number of occurrences (rows in this table) and each of these occurrences has a set of monetary values. I need all of these occurrences and their associated fields to be stretched out into one row. So Policy P1 has:

    Occ ThisValue ThatValue OtherValue...

    110025078

    220060150

    3100010065

    435562599

    ... (up to 45)

    And it has to become:

    Occ ThisValue1 ThatValue1 OtherValue1 Occ ThisValue2 ThatValue2 OtherValue2 Occ3 ThisValue3 ThatValue3 OtherValue3

    1 100 250 78 2 200 60 150 3 1000 100 65

    The reason for this is that I have to create a text file from a SQL 2005 database. I'm using an SSIS package to create the file. I had been planning on using a view to present the data, ready for the package to export to the file. So, I was thinking a pivot table for the above problem but I can't make it work; could be because I've never had to use a pivot table before and maybe I'm just not doing it right.

    Now I'm think I might want to solve the above problem in the package.

    Thoughts?

    Thanks in advance for your help!

  • By the way, sorry the data isn't formatted better...

  • Based on the assumption you'e looking for a way to get the rows into separate columns, please have a look at the CrossTab article referenced in my signature.



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • I had this same issue a while back when trying to produce a list of all the area code / prefix combinations in a given state. Same principle. Anyway, the idea is to convert them as one string into a memvar then doing something with it.

    For my example, I assumed your columns were all integer, so I converted them... with string memvars you'll want to TRIM them.

    ----------------------------------------------------------------------

    [font="Courier New"]DECLARE @Codes varchar(8000)

    SET @Codes = ''

    SELECT @Codes = @Codes + CONVERT(varchar, Occ) + ' ' +

    CONVERT(varchar, ThisValue) + ' ' +

    CONVERT(varchar, ThatValue) + ' ' +

    CONVERT(varchar, OtherValue) + ' '

    FROM YourTable

    -- trim off the trailing space

    SET @Codes = LEFT(@Codes, (LEN(@Codes) - 1))

    -- show the magic

    PRINT @Codes[/font]

    ----------------------------------------------------------------------

    I hope that helps!

    Jeff

  • Thanks to both of your for your suggestions and time. It turned out to be MUCH more complicated than I realized.

    For each row I have a key, a year (as an integer), an age and seven other columns of various data. Each key could have anywwhere from ~20 to ~90 rows.

    Those rows have to be put into 45 columns, with 1 - 45 appended to the column name, one such row for each key. So:

    key, year1, age1, <other col1s>, year2, age2, <other col2s> ... year45, age45, <other col45s>

    The first 20 columns are the first 20 years (1 - 20, easy-peasy).

    The next 15 rows are years 25+, counting by 5s.

    The last 10 are ages 65 - 100 by 5s, then 120 and these have to be in predetermined colums; data for age 65 has to be in the set of "36" columns, age 70 in "37" columns. So if a key doesn't have an age 65 row, the "36" columns have to be null.

    I have a big, fat, ugly but code with cursors and such, but it works. Fortunately the datasets aren't very large; hundreds of rows.

    I'll try to find some time to condense and de-identify the code so I can post it. I'd like to find a more elegant (and less embarrassing from a code standpoint) solution.

    Thanks! Hope that makes sense!

  • I generated some data to use as a test ...

    CREATE TABLE #TEST

    (recIDINT PRIMARY KEY

    ,theKeyVARCHAR(10)

    ,theYearINT

    ,theValue INT)

    INSERT INTO #TEST

    SELECT 1,'key 2',1934,50 UNION ALL

    SELECT 2,'key 2',1935,30 UNION ALL

    SELECT 3,'key 2',1936,96 UNION ALL

    SELECT 4,'key 2',1937,105 UNION ALL

    SELECT 5,'key 2',1938,16 UNION ALL

    SELECT 6,'key 2',1939,13 UNION ALL

    SELECT 7,'key 2',1940,85 UNION ALL

    SELECT 8,'key 2',1941,26 UNION ALL

    SELECT 9,'key 2',1942,65 UNION ALL

    SELECT 10,'key 2',1943,61 UNION ALL

    SELECT 11,'key 2',1944,125 UNION ALL

    SELECT 12,'key 2',1945,111 UNION ALL

    SELECT 13,'key 2',1946,126 UNION ALL

    SELECT 14,'key 2',1947,103 UNION ALL

    SELECT 15,'key 1',1947,46 UNION ALL

    SELECT 16,'key 1',1948,12 UNION ALL

    SELECT 17,'key 2',1948,60 UNION ALL

    SELECT 18,'key 2',1949,51 UNION ALL

    SELECT 19,'key 1',1949,43 UNION ALL

    SELECT 20,'key 2',1950,3 UNION ALL

    SELECT 21,'key 1',1950,57 UNION ALL

    SELECT 22,'key 1',1951,70 UNION ALL

    SELECT 23,'key 2',1951,40 UNION ALL

    SELECT 24,'key 2',1952,38 UNION ALL

    SELECT 25,'key 1',1952,73 UNION ALL

    SELECT 26,'key 2',1953,23 UNION ALL

    SELECT 27,'key 1',1953,96 UNION ALL

    SELECT 28,'key 2',1954,100 UNION ALL

    SELECT 29,'key 1',1954,3 UNION ALL

    SELECT 30,'key 2',1955,110 UNION ALL

    SELECT 31,'key 1',1955,94 UNION ALL

    SELECT 32,'key 1',1956,7 UNION ALL

    SELECT 33,'key 2',1956,6 UNION ALL

    SELECT 34,'key 1',1957,69 UNION ALL

    SELECT 35,'key 2',1957,121 UNION ALL

    SELECT 36,'key 2',1958,48 UNION ALL

    SELECT 37,'key 1',1958,85 UNION ALL

    SELECT 38,'key 1',1959,9 UNION ALL

    SELECT 39,'key 2',1959,43 UNION ALL

    SELECT 40,'key 2',1960,98 UNION ALL

    SELECT 41,'key 3',1960,7 UNION ALL

    SELECT 42,'key 1',1960,87 UNION ALL

    SELECT 43,'key 1',1961,48 UNION ALL

    SELECT 44,'key 3',1961,66 UNION ALL

    SELECT 45,'key 2',1961,93 UNION ALL

    SELECT 46,'key 1',1962,67 UNION ALL

    SELECT 47,'key 3',1962,10 UNION ALL

    SELECT 48,'key 2',1962,45 UNION ALL

    SELECT 49,'key 2',1963,66 UNION ALL

    SELECT 50,'key 3',1963,71 UNION ALL

    SELECT 51,'key 1',1963,52 UNION ALL

    SELECT 52,'key 2',1964,86 UNION ALL

    SELECT 53,'key 3',1964,12 UNION ALL

    SELECT 54,'key 1',1964,13 UNION ALL

    SELECT 55,'key 2',1965,118 UNION ALL

    SELECT 56,'key 3',1965,84 UNION ALL

    SELECT 57,'key 1',1965,15 UNION ALL

    SELECT 58,'key 3',1966,19 UNION ALL

    SELECT 59,'key 2',1966,8 UNION ALL

    SELECT 60,'key 1',1966,60 UNION ALL

    SELECT 61,'key 1',1967,55 UNION ALL

    SELECT 62,'key 3',1967,61 UNION ALL

    SELECT 63,'key 2',1967,53 UNION ALL

    SELECT 64,'key 1',1968,37 UNION ALL

    SELECT 65,'key 3',1968,87 UNION ALL

    SELECT 66,'key 2',1968,21 UNION ALL

    SELECT 67,'key 1',1969,78 UNION ALL

    SELECT 68,'key 3',1969,57 UNION ALL

    SELECT 69,'key 2',1969,58 UNION ALL

    SELECT 70,'key 1',1970,1 UNION ALL

    SELECT 71,'key 2',1970,113 UNION ALL

    SELECT 72,'key 3',1970,29 UNION ALL

    SELECT 73,'key 2',1971,11 UNION ALL

    SELECT 74,'key 1',1971,31 UNION ALL

    SELECT 75,'key 3',1971,49 UNION ALL

    SELECT 76,'key 2',1972,108 UNION ALL

    SELECT 77,'key 1',1972,36 UNION ALL

    SELECT 78,'key 3',1972,5 UNION ALL

    SELECT 79,'key 1',1973,6 UNION ALL

    SELECT 80,'key 2',1973,95 UNION ALL

    SELECT 81,'key 3',1973,14 UNION ALL

    SELECT 82,'key 3',1974,78 UNION ALL

    SELECT 83,'key 2',1974,90 UNION ALL

    SELECT 84,'key 1',1974,16 UNION ALL

    SELECT 85,'key 3',1975,80 UNION ALL

    SELECT 86,'key 1',1975,40 UNION ALL

    SELECT 87,'key 2',1975,28 UNION ALL

    SELECT 88,'key 3',1976,47 UNION ALL

    SELECT 89,'key 1',1976,25 UNION ALL

    SELECT 90,'key 2',1976,76 UNION ALL

    SELECT 91,'key 1',1977,33 UNION ALL

    SELECT 92,'key 2',1977,33 UNION ALL

    SELECT 93,'key 3',1977,21 UNION ALL

    SELECT 94,'key 2',1978,83 UNION ALL

    SELECT 95,'key 1',1978,90 UNION ALL

    SELECT 96,'key 3',1978,36 UNION ALL

    SELECT 97,'key 2',1979,71 UNION ALL

    SELECT 98,'key 1',1979,84 UNION ALL

    SELECT 99,'key 3',1979,59 UNION ALL

    SELECT 100,'key 1',1980,72 UNION ALL

    SELECT 101,'key 3',1980,56 UNION ALL

    SELECT 102,'key 2',1980,91 UNION ALL

    SELECT 103,'key 3',1981,3 UNION ALL

    SELECT 104,'key 2',1981,70 UNION ALL

    SELECT 105,'key 1',1981,22 UNION ALL

    SELECT 106,'key 3',1982,17 UNION ALL

    SELECT 107,'key 1',1982,18 UNION ALL

    SELECT 108,'key 2',1982,56 UNION ALL

    SELECT 109,'key 3',1983,52 UNION ALL

    SELECT 110,'key 2',1983,115 UNION ALL

    SELECT 111,'key 1',1983,49 UNION ALL

    SELECT 112,'key 3',1984,77 UNION ALL

    SELECT 113,'key 2',1984,10 UNION ALL

    SELECT 114,'key 1',1984,51 UNION ALL

    SELECT 115,'key 1',1985,93 UNION ALL

    SELECT 116,'key 2',1985,31 UNION ALL

    SELECT 117,'key 3',1985,24 UNION ALL

    SELECT 118,'key 1',1986,42 UNION ALL

    SELECT 119,'key 2',1986,123 UNION ALL

    SELECT 120,'key 3',1986,54 UNION ALL

    SELECT 121,'key 3',1987,85 UNION ALL

    SELECT 122,'key 1',1987,30 UNION ALL

    SELECT 123,'key 2',1987,80 UNION ALL

    SELECT 124,'key 3',1988,40 UNION ALL

    SELECT 125,'key 1',1988,28 UNION ALL

    SELECT 126,'key 2',1988,78 UNION ALL

    SELECT 127,'key 2',1989,101 UNION ALL

    SELECT 128,'key 1',1989,58 UNION ALL

    SELECT 129,'key 3',1989,63 UNION ALL

    SELECT 130,'key 2',1990,88 UNION ALL

    SELECT 131,'key 3',1990,8 UNION ALL

    SELECT 132,'key 1',1990,75 UNION ALL

    SELECT 133,'key 3',1991,50 UNION ALL

    SELECT 134,'key 1',1991,63 UNION ALL

    SELECT 135,'key 2',1991,120 UNION ALL

    SELECT 136,'key 2',1992,41 UNION ALL

    SELECT 137,'key 1',1992,54 UNION ALL

    SELECT 138,'key 3',1992,82 UNION ALL

    SELECT 139,'key 2',1993,35 UNION ALL

    SELECT 140,'key 3',1993,33 UNION ALL

    SELECT 141,'key 1',1993,61 UNION ALL

    SELECT 142,'key 3',1994,35 UNION ALL

    SELECT 143,'key 2',1994,36 UNION ALL

    SELECT 144,'key 1',1994,21 UNION ALL

    SELECT 145,'key 2',1995,46 UNION ALL

    SELECT 146,'key 1',1995,19 UNION ALL

    SELECT 147,'key 3',1995,70 UNION ALL

    SELECT 148,'key 2',1996,1 UNION ALL

    SELECT 149,'key 3',1996,15 UNION ALL

    SELECT 150,'key 1',1996,10 UNION ALL

    SELECT 151,'key 3',1997,1 UNION ALL

    SELECT 152,'key 1',1997,88 UNION ALL

    SELECT 153,'key 2',1997,68 UNION ALL

    SELECT 154,'key 2',1998,15 UNION ALL

    SELECT 155,'key 1',1998,76 UNION ALL

    SELECT 156,'key 3',1998,31 UNION ALL

    SELECT 157,'key 3',1999,43 UNION ALL

    SELECT 158,'key 2',1999,25 UNION ALL

    SELECT 159,'key 1',1999,82 UNION ALL

    SELECT 160,'key 2',2000,20 UNION ALL

    SELECT 161,'key 1',2000,81 UNION ALL

    SELECT 162,'key 3',2000,45 UNION ALL

    SELECT 163,'key 2',2001,55 UNION ALL

    SELECT 164,'key 1',2001,39 UNION ALL

    SELECT 165,'key 3',2001,38 UNION ALL

    SELECT 166,'key 3',2002,22 UNION ALL

    SELECT 167,'key 2',2002,5 UNION ALL

    SELECT 168,'key 1',2002,79 UNION ALL

    SELECT 169,'key 1',2003,66 UNION ALL

    SELECT 170,'key 3',2003,64 UNION ALL

    SELECT 171,'key 2',2003,106 UNION ALL

    SELECT 172,'key 1',2004,27 UNION ALL

    SELECT 173,'key 3',2004,89 UNION ALL

    SELECT 174,'key 2',2004,81 UNION ALL

    SELECT 175,'key 2',2005,73 UNION ALL

    SELECT 176,'key 3',2005,75 UNION ALL

    SELECT 177,'key 1',2005,24 UNION ALL

    SELECT 178,'key 2',2006,128 UNION ALL

    SELECT 179,'key 1',2006,64 UNION ALL

    SELECT 180,'key 3',2006,42 UNION ALL

    SELECT 181,'key 2',2007,18 UNION ALL

    SELECT 182,'key 1',2007,4 UNION ALL

    SELECT 183,'key 3',2007,28 UNION ALL

    SELECT 184,'key 3',2008,26 UNION ALL

    SELECT 185,'key 1',2008,45 UNION ALL

    SELECT 186,'key 2',2008,75 UNION ALL

    SELECT 187,'key 2',2009,116 UNION ALL

    SELECT 188,'key 1',2009,91 UNION ALL

    SELECT 189,'key 3',2009,68 UNION ALL

    SELECT 190,'key 1',2010,34 UNION ALL

    SELECT 191,'key 3',2010,73 UNION ALL

    SELECT 192,'key 2',2010,63

    I didn't extend this all the way out to the 45th column set but hopefully you get the idea ...

    SELECT theKey,

    Year1 = MAX(CASE WHEN yearOrder = 1 THEN theYear END),

    Value1 = MAX(CASE WHEN yearOrder = 1 THEN theValue END),

    Year2 = MAX(CASE WHEN yearOrder = 2 THEN theYear END),

    Value2 = MAX(CASE WHEN yearOrder = 2 THEN theValue END),

    Year3 = MAX(CASE WHEN yearOrder = 3 THEN theYear END),

    Value3 = MAX(CASE WHEN yearOrder = 3 THEN theValue END),

    Year4 = MAX(CASE WHEN yearOrder = 4 THEN theYear END),

    Value4 = MAX(CASE WHEN yearOrder = 4 THEN theValue END),

    Year5 = MAX(CASE WHEN yearOrder = 5 THEN theYear END),

    Value5 = MAX(CASE WHEN yearOrder = 5 THEN theValue END),

    Year6 = MAX(CASE WHEN yearOrder = 6 THEN theYear END),

    Value6 = MAX(CASE WHEN yearOrder = 6 THEN theValue END),

    Year7 = MAX(CASE WHEN yearOrder = 7 THEN theYear END),

    Value7 = MAX(CASE WHEN yearOrder = 7 THEN theValue END),

    Year8 = MAX(CASE WHEN yearOrder = 8 THEN theYear END),

    Value8 = MAX(CASE WHEN yearOrder = 8 THEN theValue END),

    Year9 = MAX(CASE WHEN yearOrder = 9 THEN theYear END),

    Value9 = MAX(CASE WHEN yearOrder = 9 THEN theValue END),

    Year10 = MAX(CASE WHEN yearOrder = 10 THEN theYear END),

    Value10 = MAX(CASE WHEN yearOrder = 10 THEN theValue END),

    Year11 = MAX(CASE WHEN yearOrder = 11 THEN theYear END),

    Value11 = MAX(CASE WHEN yearOrder = 11 THEN theValue END),

    Year12 = MAX(CASE WHEN yearOrder = 12 THEN theYear END),

    Value12 = MAX(CASE WHEN yearOrder = 12 THEN theValue END),

    Year13 = MAX(CASE WHEN yearOrder = 13 THEN theYear END),

    Value13 = MAX(CASE WHEN yearOrder = 13 THEN theValue END),

    Year14 = MAX(CASE WHEN yearOrder = 14 THEN theYear END),

    Value14 = MAX(CASE WHEN yearOrder = 14 THEN theValue END),

    Year15 = MAX(CASE WHEN yearOrder = 15 THEN theYear END),

    Value15 = MAX(CASE WHEN yearOrder = 15 THEN theValue END),

    Year16 = MAX(CASE WHEN yearOrder = 16 THEN theYear END),

    Value16 = MAX(CASE WHEN yearOrder = 16 THEN theValue END),

    Year17 = MAX(CASE WHEN yearOrder = 17 THEN theYear END),

    Value17 = MAX(CASE WHEN yearOrder = 17 THEN theValue END),

    Year18 = MAX(CASE WHEN yearOrder = 18 THEN theYear END),

    Value18 = MAX(CASE WHEN yearOrder = 18 THEN theValue END),

    Year19 = MAX(CASE WHEN yearOrder = 19 THEN theYear END),

    Value19 = MAX(CASE WHEN yearOrder = 19 THEN theValue END),

    Year20 = MAX(CASE WHEN yearOrder = 20 THEN theYear END),

    Value20 = MAX(CASE WHEN yearOrder = 20 THEN theValue END),

    Year25 = MAX(CASE WHEN yearOrder = 25 THEN theYear END),

    Value25 = MAX(CASE WHEN yearOrder = 25 THEN theValue END),

    Year30 = MAX(CASE WHEN yearOrder = 30 THEN theYear END),

    Value30 = MAX(CASE WHEN yearOrder = 30 THEN theValue END),

    Year35 = MAX(CASE WHEN yearOrder = 35 THEN theYear END),

    Value35 = MAX(CASE WHEN yearOrder = 35 THEN theValue END),

    Year40 = MAX(CASE WHEN yearOrder = 40 THEN theYear END),

    Value40 = MAX(CASE WHEN yearOrder = 40 THEN theValue END),

    Year45 = MAX(CASE WHEN yearOrder = 45 THEN theYear END),

    Value45 = MAX(CASE WHEN yearOrder = 45 THEN theValue END),

    Year50 = MAX(CASE WHEN yearOrder = 50 THEN theYear END),

    Value50 = MAX(CASE WHEN yearOrder = 50 THEN theValue END)

    FROM

    (SELECT recID,

    theKey,

    theYear,

    theValue,

    ROW_NUMBER() OVER(PARTITION BY theKey ORDER BY theYear) as yearOrder

    FROM #TEST) sq

    GROUP BY theKey

    ORDER BY theKey

    You can do the same basic thing with the PIVOT operator, but I don't think it's particularly efficient.

    └> bt



    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • One approach is to concatenate Occ, ThisValue, ThatValue, OtherValue into one block and then to dynamically PIVOT the data based on this concatenated block and the rows present. Then in the final SELECT statement, the Occ, ThisValue, ThatValue, OtherValue components can be unwrapped by string manipulation

    IF NOT OBJECT_ID('tempdb.dbo.#Policy') IS NULL DROP TABLE #Policy

    SELECT 1 AS Occ, 100 AS ThisValue, 250 AS ThatValue, 78 AS OtherValue INTO #Policy

    UNION ALL SELECT 2, 200, 60, 150

    UNION ALL SELECT 3, 1000, 100, 65

    UNION ALL SELECT 4, 355, 625, 99

    ;

    DECLARE @DetailCount INT

    DECLARE @SQL NVARCHAR(MAX)

    DECLARE @DetailList VARCHAR(MAX)

    DECLARE @DetailSplit VARCHAR(MAX)

    DECLARE @i INT

    DECLARE @ivarchar VARCHAR(10)

    SELECT @DetailCount = MAX(Occ) FROM #Policy

    SET @DetailList = ''

    SET @DetailSplit = ''

    SET @i = 1

    WHILE @i <= @DetailCount

    BEGIN

    SET @ivarchar = CONVERT(VARCHAR(10), @i)

    SET @DetailList = @DetailList + '[' + @ivarchar + '],'

    SET @DetailSplit = @DetailSplit + 'LEFT([' + @ivarchar + '], CHARINDEX(''|'', [' + @ivarchar + ']) - 1) AS Occ' + @ivarchar + ',' + CHAR(13) + CHAR(10) +

    'SUBSTRING([' + @ivarchar + '], CHARINDEX(''|'', [' + @ivarchar + ']) + 1, CHARINDEX(''|'', [' + @ivarchar + '], CHARINDEX(''|'', [' + @ivarchar + ']) + 1) - CHARINDEX(''|'', [' + @ivarchar + ']) - 1) AS ThisValue' + @ivarchar + ',' + CHAR(13) + CHAR(10) +

    'REVERSE(SUBSTRING(REVERSE([' + @ivarchar + ']), CHARINDEX(''|'', REVERSE([' + @ivarchar + '])) + 1, CHARINDEX(''|'', REVERSE([' + @ivarchar + ']), CHARINDEX(''|'', REVERSE([' + @ivarchar + '])) + 1) - CHARINDEX(''|'', REVERSE([' + @ivarchar + '])) - 1)) AS ThatValue' + @ivarchar + ',' + CHAR(13) + CHAR(10) +

    'REVERSE(LEFT(REVERSE([' + @ivarchar + ']), CHARINDEX(''|'', REVERSE([' + @ivarchar + '])) - 1)) AS OtherValue' + @ivarchar + ',' + CHAR(13) + CHAR(10)

    SET @i = @i + 1

    END

    SET @DetailList = LEFT(@DetailList, LEN(@DetailList) - 1)

    SET @DetailSplit = LEFT(@DetailSplit, LEN(@DetailSplit) - 3)

    SET @SQL =

    '

    ;

    WITH

    PolicyCombine AS

    (

    SELECT

    Occ,

    CONVERT(VARCHAR, Occ) + ''|'' + CONVERT(VARCHAR, ThisValue) + ''|'' + CONVERT(VARCHAR, ThatValue) + ''|'' + CONVERT(VARCHAR, OtherValue) AS Details FROM #Policy

    )

    ,

    ctePivot AS

    (

    SELECT *

    FROM PolicyCombine

    PIVOT

    (

    MAX(Details) FOR Occ IN

    (

    ' + @DetailList + '

    )

    )

    AS pvt

    )

    SELECT

    ' + @DetailSplit + '

    FROM ctePivot

    '

    EXEC SP_EXECUTESQL @SQL, N'@innerDetailList VARCHAR(MAX), @innerDetailSplit VARCHAR(MAX)', @DetailList, @DetailSplit

  • Steve, I would highly suggest that you read Jeff Moden's article on pivots and crosstabs[/url], especially the section on performance at the end. If you're not only using a PIVOT, but you're also doing string functions and using dynamic sql there is pretty much no way you're going to come close the the performance of a crosstab (as in my example above.) Also, in my opinion, the PIVOT/string function/dynamic sql is going to be harder to maintain as it is harder to read.

    The crosstab may look like it's a lot of work because it's more lines of code, but it's just a matter of copy and paste along with cntrl+H in SSMS.

    └> bt



    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • Thanks, bteraberry! That is orders of magnitude simpler than what I came up with! I still have to tweak it a bit because the criteria changes twice within the 45 rows-into-columns, but this is a great start.

    Thanks again!

  • I've use the COALESCE function to do what you've done, although I've only had to flatten one column. You may be able to use this. The code sample below is from somewhere on the internet, not my work.

    declare @FinalString nvarchar(250)

    SET @FinalString=''

    SELECT @FinalString = COALESCE(@FinalString + ', ', '') + (CAST([name] AS nvarchar(50)))

    FROM Fruit

    order by [name]

    SELECT REPLACE(@FinalString,'''',' ')

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply