question

Jenn S avatar image
Jenn S asked

Is there a way to change the columns to rows in a stored procedure?

Here is simple table setup TableName
DocNum
DocTitle
DocAcronym
Narrative

Data is dispayed for a select * from TableName
DocNum DocTitle DocAcronym Narrative
100 Title 1 TestAcro1 Narrative1
101 Title 2 TestAcro2 Narrative 2

I would like the data to be displayed like this where the DocNum is the Column and the database fields are the rows
DocNum        100           101
DocTitle         Title1         Title2
DocAcronym TestAcro1   TestAcro2
Narrative       Narrative1   Narrative2

I have looked at pivot and unpivot but not sure how to get this to work with my situation.

sql-server-2005stored-procedures
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jay D avatar image
Jay D answered

Jenn,

I believe the PIVOT function requires an aggregator (AVG, SUM, etc) in order to work. In your example you could potentially have millions of rows of data for which SQL Server couldn't provide columns (4,096 I think). Are you sure you need to pivot this data? How would you display such a wide data set that would eat more real estate than a page possibly has?

Regards,

Jay

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Scot Hauder avatar image
Scot Hauder answered

I agree with Jay, but if your intention is to paste this into Excel, you could try something like this:

            
SELECT CAST(DocNum AS varchar(10)) + CHAR(9)            
FROM TableName            
UNION ALL SELECT CHAR(13)            
UNION ALL            
SELECT DocTitle + CHAR(9)            
FROM TableName            
UNION ALL SELECT CHAR(13)            
UNION ALL            
SELECT DocAcronym + CHAR(9)            
FROM TableName            
UNION ALL SELECT CHAR(13)            
UNION ALL            
SELECT Narrative + CHAR(9)            
FROM TableName            
WHERE DocNum IN (100,101)            
FOR XML PATH('')            
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Scot Hauder avatar image
Scot Hauder answered
var ds = new DataSet();            
var sb = new StringBuilder();            
            
string[] DocArray = null;            
string Docs = null;            
DocArray = new string[] {"101","102"}; //list of docs to display            
Docs = string.Join(",", DocArray);            
            
string sqlstr = "SELECT( " +             
                "SELECT CAST('<Doc' + CAST(DocNum AS VARCHAR(10)) + '>' + CAST(DocNum AS VARCHAR(10)) + '</Doc' + CAST(DocNum AS VARCHAR(10)) + '>' AS XML) " +             
                "FROM TABLENAME " +             
                "WHERE DocNum IN (" + Docs + ") " +             
                "ORDER BY DocNum " +            
                "FOR XML PATH(''), ROOT('row')) " +             
                "UNION ALL SELECT( " +             
                "SELECT CAST('<Doc' + CAST(DocNum AS VARCHAR(10)) + '>' + DocTitle + '</Doc' + CAST(DocNum AS VARCHAR(10)) + '>' AS XML) " +             
                "FROM TABLENAME " +             
                "WHERE DocNum IN (" + Docs + ") " +             
                "ORDER BY DocNum " +            
                "FOR XML PATH(''), ROOT('row')) " +             
                "UNION ALL SELECT( " +             
                "SELECT CAST('<Doc' + CAST(DocNum AS VARCHAR(10)) + '>' + DocAcronym + '</Doc' + CAST(DocNum AS VARCHAR(10)) + '>' AS XML) " +             
                "FROM TABLENAME " +             
                "WHERE DocNum IN (" + Docs + ") " +             
                "ORDER BY DocNum " +            
                "FOR XML PATH(''), ROOT('row')) " +             
                "UNION ALL SELECT( " +             
                "SELECT CAST('<Doc' + CAST(DocNum AS VARCHAR(10)) + '>' + Narrative + '</Doc' + CAST(DocNum AS VARCHAR(10)) + '>' AS XML) " +             
                "FROM TABLENAME " +             
                "WHERE DocNum IN (" + Docs + ") " +             
                "ORDER BY DocNum " +            
                "FOR XML PATH(''), ROOT('row'))";            
            
using (SqlConnection conn = new SqlConnection(connString)) {            
    using (SqlCommand cmd = new SqlCommand(sqlstr, conn)) {            
            
        conn.Open();            
        SqlDataReader dr = cmd.ExecuteReader();            
            
        sb.Append("<Data>");            
        if (dr.HasRows) {            
            while (dr.Read()) {            
                sb.Append(dr[0].ToString());            
            }            
        }            
        sb.Append("</Data>");            
    }            
}            
            
var theReader = new StringReader(sb.ToString());            
ds.ReadXml(theReader);            
            
dataGridView1.DataSource = ds.Tables[0].DefaultView;            
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.